Re: Bypassing NULL elements in row_to_json function

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Bypassing NULL elements in row_to_json function
Дата
Msg-id CAKFQuwaJi5p9gwz79AcBwA018=1aXzgMadDQSnPJtARup7MCWQ@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>)
Re: Bypassing NULL elements in row_to_json function  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список pgsql-general
On Sun, Apr 10, 2016 at 8:39 AM, Michael Nolan <htfoot@gmail.com> wrote:
Here's what I did:

 \d gold1604_test
Table "uscf.gold1604_test"
 Column | Type | Modifiers
--------+------+-----------
 data   | json |

Some sample data:
 {"id":"10000001","name":"MISNER, J NATHAN","st":"NY","exp":"2012-05-31","sts":
"A"} +

  {"id":"10000002","name":"MISNER, JUDY","st":"TN","exp":"2007-07-31","sts":"I"}
     +

  {"id":"10000003","name":"MISNER, J AMSCHEL","st":"NY","exp":"2007-05-31","sts"
:"A"}+


​(I think) PostgreSQL assumes that there is only a single top-level json element, whether it be an array or an object.  The first thing you'd have to do is split on the newline and create a PostgreSQL text array.

But,
uscf=> insert into goldmast_test select * from json_populate_record(NULL::"goldmast_test", (select * from gold1604_test limit 2) )
uscf-> \g
ERROR:  more than one row returned by a subquery used as an expression

Is there a way to get around the one row per subquery issue?

​Yes, use LATERAL.

Something like the following should work (not tested):

INSERT INTO goldmast_test
SELECT jpr.* 
FROM gold1604_test 
LATERAL json_populate_record(null::goldmast_test", data) AS jpr

ideally you could just do (not tested):

INSERT INTO goldmast_test
SELECT jpr.* 
FROM (SELECT t::json FROM unnest(string_to_array(?::text, E'\n') un (t))) src j
LATERAL json_populate_record(null::goldmast_test", j) AS jpr

Where the "?::text" is placeholder for the textual JSON being handed to the query thus avoiding the temporary "gold1604_test" table.

David J.

В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: Really unique session ID - PID + connection timestamp?
Следующее
От: Michael Nolan
Дата:
Сообщение: Re: Bypassing NULL elements in row_to_json function