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
