Thread: passing multiple records to json_populate_recordset

On Mon, Sep 23, 2013 at 8:25 AM, Raphael Bauduin <rblists@gmail.com> wrote:
>
>
> 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?):

you need to use LATERAL.

here's a summary of the technique (see lateral version -- you don't
need to use recursion).

http://www.reddit.com/r/PostgreSQL/comments/1hwu8i/postgresql_recursive_common_table_expression_and/caywoxw

melrin

On 09/23/2013 06:25 AM, Raphael Bauduin wrote:
>
>
> 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?):

Maybe try:

select  * from
json_populate_record(null::product,row_to_json(json_populate_recordset(null::product,event->'products')))
from (select * from events limit 2) as foo ;

>
> Raph


--
Adrian Klaver
adrian.klaver@gmail.com

On Mon, Sep 23, 2013 at 8:33 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Mon, Sep 23, 2013 at 8:25 AM, Raphael Bauduin <rblists@gmail.com> wrote:
>>
>>
>> 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?):
>
> you need to use LATERAL.
>
> here's a summary of the technique (see lateral version -- you don't
> need to use recursion).
>
> http://www.reddit.com/r/PostgreSQL/comments/1hwu8i/postgresql_recursive_common_table_expression_and/caywoxw

follow up:

Raphael hit me up off list for more detail so I thought I'd post the query here:

select p.* from (select event from events limit 10) src CROSS JOIN
LATERAL json_populate_recordset(null::product,src.event->'products' )
p;

merlin