Обсуждение: json_populate_recordset and nested object, how to solve?
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.
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.
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
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
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: 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