Re: json_populate_recordset and nested object, how to solve?

MatheusOl helped me solve this on IRC, sending it here in case it helps
someone looking at the archives of the mailing list.

Here is a test case

create table t(id SERIAL, event json);
insert into t(event) VALUES ('{"type":"show", "products": [ { "id" :
1, "name" : "p1"}] }'::json);
insert into t(event) VALUES ('{"type":"show", "products": [ { "id" :
1, "name" : "p1" , "stock" : [ {"XL" : 1}] }] }'::json);
create type product as (id int, name text );

select rs.* from (select * from t where id=1) e CROSS JOIN LATERAL
json_populate_recordset(null::product, e.event->'products') rs;--works
select rs.* from (select * from t where id=2) e CROSS JOIN LATERAL
json_populate_recordset(null::product, e.event->'products') rs;--
error:
ERROR: cannot call json_populate_recordset on a nested object

and the simple solution I was looking for:

SELECT (p->>'id')::int AS id, p->>'name' AS name FROM (SELECT
json_array_elements(event->'products') AS p FROM t) t1;

Raph

On Fri, Apr 4, 2014 at 10:25 AM, Raphael Bauduin <rblists(at)gmail(dot)com> wrote:

> Hi,
>
> here is an example of a problem I encounter with json_populate_recordset
> because it does not support nested object. Actually, I would prefer that it
> ignores nested object rather than raise an error, as it fails on a key I
> don't even want to use!
>
> Here's the query:
>
> select e.timestamp::date, e.user_id, rs.similarity from
> (select * from events where type='suggestion' and timestamp<'2014-04-04'
> and timestamp>'2014-04-03') e
> CROSS JOIN LATERAL
> json_populate_recordset(null::suggestion, event->'products') rs
> order by e.user_id;
>
>
> event->'products' is an array of json objects, one of this keys (stock)
> being an array of json objects. I can absolutely ignore that key in this
> query, but I don't see how. The suggestion type does not have a stock key,
> so it would be absent of the result anyway.
>
> So, how would you get event->'products' without the stock keys, just to be
> able to call json_populate_recordset?
>
> Thanks.
>
> Raph
>
> PS: this might be seen as a followup to a previous mail thread:
> http://www.postgresql.org/message-id/CAONrwUGMQthsut_F8X4CBGQDuKa5=A+AtmxSXb2FdOXh5PD3Qg@mail.gmail.com
> but I don't see how to apply that suggestion here.
>
>

--
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.org