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@gmail.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:
/messages/by-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