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 <rbli...@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:
> http://www.postgresql.org/message-id/CAONrwUGMQthsut_F8X4CBGQDuKa5=a+atmxsxb2fdoxh5pd...@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
