Re: Bypassing NULL elements in row_to_json function

On 04/10/2016 08:39 AM, Michael Nolan 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"}+
>
>
> uscf-> \d goldmast_test
>           Table "uscf.goldmast_test"
>   Column |         Type          | Modifiers
> --------+-----------------------+-----------
>   id     | character varying(8)  |
>   name   | character varying(40) |
>   st     | character varying(2)  |
>   exp    | date                  |
>   sts    | character(1)          |
>   supp   | date                  |
>   rrtg   | character varying(8)  |
>   qrtg   | character varying(8)  |
>   brtg   | character varying(8)  |
>   oqrtg  | character varying(8)  |
>   obrtg  | character varying(8)  |
>   fid    | character varying(12) |
>
>
>
>
> insert into goldmast_test select * from
> json_populate_record(NULL::"goldmast_test", (select * from gold1604_test
> limit 1) )
>   produces:
> uscf=> 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   |      |      |      |
>   |       |       |
> (1 row)
>
> The fact that the null values were stripped out is not an issue here.
>
> 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?

Per Davids post:

http://www.postgresql.org/docs/9.5/interactive/functions-json.html

json_populate_recordset(base anyelement, from_json json)

Expands the outermost array of objects in from_json to a set of rows
whose columns match the record type defined by base (see note below).
select * from json_populate_recordset(null::myrowtype,
'[{"a":1,"b":2},{"a":3,"b":4}]')

> --
> Mike Nolan


--
Adrian Klaver
adrian.klaver@aklaver.com