Re: Bypassing NULL elements in row_to_json function

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