passing multiple records to json_populate_recordset

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?

Thanks

Raph

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general