On 04/10/2016 07:49 AM, Michael Nolan wrote:
On Sun, Apr 10, 2016 at 2:30 AM, David G. Johnston <david.g.johnston@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx>> wrote: On Sat, Apr 9, 2016 at 9:48 PM, Michael Nolan <htfoot@xxxxxxxxx <mailto:htfoot@xxxxxxxxx>>wrote: 2nd Followup: It turns out that loading a table from a JSON string is more complicated than going from a table to JSON, perhaps for good reason. There does not appear to be a direct inverse to the row_to_json() function, but it wasn't difficult for me to write a PHP program that takes the JSON file I created the other day and converts it back to a series of inserts, recreating the original table. Of course this simple program does NO validation (not that this file needed any), so if the JSON string is not well-formed for any of a number of reasons, or if it is not properly mapped to the table into which the inserts are made, an insert could fail or result in incorrect data. -- Mike Nolan See: http://www.postgresql.org/docs/9.5/interactive/functions-json.html json_populate_record(base anyelement, from_json json) json_populate_recordset(base anyelement, from_json json) Exists in 9.3 too...though if you are going heavy json I'd suggest doing whatever you can to keep up with the recent releases. David J. If there's a way to use the json_populate_record() or json_populate_recordset() functions to load a table from a JSON file (eg, using copy), it would be nice if it was better documented. I did find a tool that loads a JSON file into a table (pgfutter), and even loaded one row from that table into another table using json_populate_record(), but the 'subquery returned multiple rows' issue wouldn't let me do the entire table.
Does the receiving table have the same structure as the sending table? Is the receiving table already populated with data?
But that still doesn't deal with validating individual fields or checking that the JSON is complete and consistent with the table to be loaded.
Well you know the JSON is not complete as you dropped all the fields in each row that had NULL values. Validation is a more complex subject and honestly something I do not think could be accomplished in straight SQL. In other words it would need to be run through some ETL tool. I use Python so as an example:
https://petl.readthedocs.org/en/latest/ In particular: https://petl.readthedocs.org/en/latest/io.html#json-files https://petl.readthedocs.org/en/latest/transform.html#validation
-- Mike Nolan
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
