On Mon, Sep 23, 2013 at 11:31 AM, Raphael Bauduin <rblists@gmail.com> wrote:
Hi,
I'm experimenting with the json data type and functions in 9.3. I'm storing json objects of this form in the event column: {type: 'event_type, products : [ {id:45, 'type': 3, 'gender':'F',..}, ..., {....} ] }
I can issue this query, but notice the limit 1:
select * from json_populate_recordset(null::product, (select event->'products' from events limit 1));
The result is (edited for conciseness):
type | gender | id ------+--------+------- 41 | F | 40003 41 | F | 60043 41 | F | 27363 41 | F | 27373 41 | F | 28563
But all these products come from one event. Is there a way to return the products from several events?, eg with a limit 2 rather than limit 1?
Some more info, after searching further.
This query
select json_populate_record(null::product,row_to_json(json_populate_recordset(null::product,event->'products'))) from (select * from events limit 2) as foo ;
returns what I want but not in the format I want (why?): json_populate_record -------------------------- (33,61,M,3,51,12,54893) (20,61,M,3,1,15,59623) (17,61,M,3,453,12,59283) (30,61,M,3,51,19,55713) (26,61,M,3,51,19,54963)
I manage to get the results as json:
select row_to_json(json_populate_recordset(null::product,event->'products')) from (select * from events limit 2) as foo ; row_to_json -------------------------------------------------------------------------------------------- {"price_advantage":33,"type":61,"gender":"M","status":3,"brand":51,"price":12,"id":54893} {"price_advantage":20,"type":61,"gender":"M","status":3,"brand":1,"price":15,"id":59623} {"price_advantage":17,"type":61,"gender":"M","status":3,"brand":453,"price":12,"id":59283}
but I don't manage to get the results as from a table like in the first json_populate_recordset query I listed (with limit 1). Any suggestion?
