Re: Bypassing NULL elements in row_to_json function
От | Adrian Klaver |
---|---|
Тема | Re: Bypassing NULL elements in row_to_json function |
Дата | |
Msg-id | 570AAA47.3030702@aklaver.com обсуждение исходный текст |
Ответ на | Re: Bypassing NULL elements in row_to_json function ("David G. Johnston" <david.g.johnston@gmail.com>) |
Список | pgsql-general |
On 04/10/2016 09:24 AM, David G. Johnston wrote: > On Sun, Apr 10, 2016 at 8:39 AM, Michael Nolan <htfoot@gmail.com > <mailto: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 I can confirm this works after a little clean up: test=> INSERT INTO goldmast_test SELECT jpr.* FROM gold1604_test, LATERAL json_populate_record(null::"goldmast_test", data) AS jpr; INSERT 0 3 test=> select * from goldmast_test ; id | name | st | exp | sts | supp | rrtg | qrtg | brtg | oqrtg | obrtg | fid ----------+-------------------+----+------------+-----+------+------+------+------+-------+-------+----- 10000001 | MISNER, J NATHAN | NY | 2012-05-31 | A | | | | | | | 10000002 | MISNER, JUDY | TN | 2007-07-31 | I | | | | | | | 10000003 | MISNER, J AMSCHEL | NY | 2007-05-31 | A | | | | | | | (3 rows) While trying to figure out how it works I discovered the LATERAL is not necessary: test=> INSERT INTO goldmast_test SELECT jpr.* FROM gold1604_test, json_populate_record(null::"goldmast_test", data) AS jpr; INSERT 0 3 test=> select * from goldmast_test ; id | name | st | exp | sts | supp | rrtg | qrtg | brtg | oqrtg | obrtg | fid ----------+-------------------+----+------------+-----+------+------+------+------+-------+-------+----- 10000001 | MISNER, J NATHAN | NY | 2012-05-31 | A | | | | | | | 10000002 | MISNER, JUDY | TN | 2007-07-31 | I | | | | | | | 10000003 | MISNER, J AMSCHEL | NY | 2007-05-31 | A | | | | | | | 10000001 | MISNER, J NATHAN | NY | 2012-05-31 | A | | | | | | | 10000002 | MISNER, JUDY | TN | 2007-07-31 | I | | | | | | | 10000003 | MISNER, J AMSCHEL | NY | 2007-05-31 | A | | | | | | | (6 rows) > > 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. > -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления: