Re: Bypassing NULL elements in row_to_json function

On Sun, Apr 10, 2016 at 8:39 AM, Michael Nolan <htfoot(at)gmail(dot)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.