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(dot)klaver(at)aklaver(dot)com