Re: Bypassing NULL elements in row_to_json function

On Sun, Apr 10, 2016 at 2:30 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

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

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