Re: Bypassing NULL elements in row_to_json function
| От | David G. Johnston |
|---|---|
| Тема | Re: Bypassing NULL elements in row_to_json function |
| Дата | |
| Msg-id | CAKFQuwZ2HuFOUs8Y4+Ppv-Nez-fhEEx9ryiaOf5Yqzk+Vt2qng@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Bypassing NULL elements in row_to_json function (Michael Nolan <htfoot@gmail.com>) |
| Ответы |
Re: Bypassing NULL elements in row_to_json function
(Michael Nolan <htfoot@gmail.com>)
|
| Список | pgsql-general |
On Sun, Apr 10, 2016 at 2:30 AM, David G. Johnston <david.g.johnston@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 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.
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.
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.
В списке pgsql-general по дате отправления:
Следующее
От: "Bannert Matthias"Дата:
Сообщение: Re: max_stack_depth problem though query is substantially smaller