Hi,
I'm trying to understand what happens here:
I have atype product defined:
=# \d product
Composite type "public.product"
Column | Type | Modifiers
-----------------+------------------+-----------
price_advantage | double precision |
type | integer |
gender | text |
status | integer |
brand | integer |
price | double precision |
id | integer |
algorithm | text |
which I'm trying to use in this query calling json_populate_recordset
=# select q.* from (select json_populate_recordset(null::product, event->'products') from events where timestamp>'2014-02-02' and type='gallery' limit 1) q;
json_populate_recordset
-----------------------------
(68,121,F,3,493,17,88753,)
This query illustrates what I want to achieve:
=# select f.* from json_populate_recordset(null::product,'[{"id":80723,"type":41,"brand":41,"price":65.0,"status":3,"price_advantage":1.0,"gender":"M", "algorithm":"v1"}]'::json) f;
price_advantage | type | gender | status | brand | price | id | algorithm
-----------------+------+--------+--------+-------+-------+-------+-----------
1 | 41 | M | 3 | 41 | 65 | 80723 | v1
I see the difference in the query ( the second working directly on the return value of the function), but in the first example, isn"t the inner returning a set, from which the outer query can do a select *?
There is a difference with the second query which I've not identified. Anyone caring to enlighten me?
Thanks
Raph
PS: to get it working, I have to write the query as this:
=# select q.* from (select * from events where timestamp>'2014-02-02' and type='gallery') q1 CROSS JOIN LATERAL json_populate_recordset(null::product, event->'products') q limit 1;
price_advantage | type | gender | status | brand | price | id | algorithm
-----------------+------+--------+--------+-------+-------+-------+-----------
68 | 121 | F | 3 | 493 | 17 | 88753 |
What I'm interested is an explanation of why this is needed.