| From: | Raphael Bauduin <rblists(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
| Subject: | json_populate_recordset and nested object, how to solve? |
| Date: | 2014-04-04 08:25:37 |
| Message-ID: | CAONrwUFRsVqpzGMg7+ga8PEJy+nKCZ1FWxaBU8WMZuaJn8AsqA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tjibbe | 2014-04-04 09:43:01 | create temp view from function inside plpgsql function. |
| Previous Message | Armand Turpel | 2014-04-04 08:20:56 | hstore - jsonb |