On Sat, Apr 9, 2016 at 9:48 PM, Michael Nolan <htfoot@gmail.com> 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. --
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.
