On Sun, Apr 10, 2016 at 2:30 AM, David G. Johnston <david.g.johnston@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 Nolanjson_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.
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.
--
Mike Nolan
