Re: Bypassing NULL elements in row_to_json function

On Sun, Apr 10, 2016 at 7:49 AM, Michael Nolan <htfoot(at)gmail(dot)com> wrote:

>
>
> 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.
>
>
It isn't that involved once you've learned generally how to call normal
record functions and also set-returning functions (that later must be in
the FROM clause of the query). If you provide what you attempted its
becomes easier to explain away your mis-understanding.

It doesn't work with COPY. You have to write an explicit INSERT+SELECT
query where the text of the JSON is a parameter. Your client library
should let you do this. If you are using "psql", which doesn't support
parameters, you up having to store the json in a psql variable and
reference that in the function.
​​
INSERT INTO %I SELECT * FROM json_populate_recordset(null::%I, $1)

​The function ensures that column order is consistent so "INSERT INTO %I"
is all you need to write.

​
​
​Data validation is why we invented CHECK constraints - if you need more
functionality than the simple mechanical conversion from a json object to a
table row you will need to write code somewhere to do the additional work.
All json_populate_record(set) promises is that the above command will work.

I suppose the way you'd write your attempt that failed would be similar to:

INSERT INTO %I
SELECT rec.* FROM src_table_with_json LATERAL
json_populate_record(null::%I, src_table_with_json.json_column);

Again, seeing what you actually did would be helpful - I'm having trouble
imaging what you did to provoke that particular error.

David J.