Re: Bypassing NULL elements in row_to_json function - Mailing list 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