returning json object with subset of keys

On Wed, Jan 8, 2014 at 6:37 AM, Raphael Bauduin <rblists@gmail.com> wrote:

Hi

I'm using the json functionalities of postgresql 9.3.
I have a query calling json_populate_recordset like this:
json_populate_recordset(null::product, event->'products')
but it returns an error:
ERROR: cannot call json_populate_recordset on a nested object

There is indeed one key in event->'products' giving access to an array of
objects.

Is there a way to specify which keys to keep from the object? I haven't
found ti in the docs.

Here is pseudo code of what I'd like to do:
json_populate_recordset(null::product, event->'products' WITH ONLY KEYS
{'f1','f2'})

unfortunately, not without manipulating the json. this is basically a
somewhat crippling limitation of the json_populate functions -- they
can't handle anything but flat tuples. so you have to do something
highly circuitous.

problem (one record):
postgres=# create table foo(a text, b text);
postgres=# select json_populate_record(null::foo, '{"a": "abc", "b":
"def", "c": [1,2,3]}'::json);
ERROR: cannot call json_populate_record on a nested object

nasty solution:
postgres=# with data as (select '{"a": "abc", "b": "def", "c":
[1,2,3]}'::json as j)
select json_populate_record(null::foo, row_to_json(q)) from
(
select j->'a' as a, j->'b' as b from data
) q;
json_populate_record
----------------------
(abc,def)

with some extra manipulations you can do a record set. basically, you
need to get the json 'right' first (or that can be done on the
client).

merlin

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

On Wed, Jan 8, 2014 at 4:05 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Wed, Jan 8, 2014 at 6:37 AM, Raphael Bauduin <rblists@gmail.com> wrote:

Hi

I'm using the json functionalities of postgresql 9.3.
I have a query calling json_populate_recordset like this:
json_populate_recordset(null::product, event->'products')
but it returns an error:
ERROR: cannot call json_populate_recordset on a nested object

There is indeed one key in event->'products' giving access to an array of
objects.

Is there a way to specify which keys to keep from the object? I haven't
found ti in the docs.

Here is pseudo code of what I'd like to do:
json_populate_recordset(null::product, event->'products' WITH ONLY KEYS
{'f1','f2'})

unfortunately, not without manipulating the json. this is basically a
somewhat crippling limitation of the json_populate functions -- they
can't handle anything but flat tuples. so you have to do something
highly circuitous.

problem (one record):
postgres=# create table foo(a text, b text);
postgres=# select json_populate_record(null::foo, '{"a": "abc", "b":
"def", "c": [1,2,3]}'::json);
ERROR: cannot call json_populate_record on a nested object

nasty solution:
postgres=# with data as (select '{"a": "abc", "b": "def", "c":
[1,2,3]}'::json as j)
select json_populate_record(null::foo, row_to_json(q)) from
(
select j->'a' as a, j->'b' as b from data
) q;
json_populate_record
----------------------
(abc,def)

with some extra manipulations you can do a record set. basically, you
need to get the json 'right' first (or that can be done on the
client).

merlin

ok, thanks for your reply.
Is this considered to be added in the future to the json functions
available? I could use it frequently I think.

Cheers

raph

On Thu, Jan 9, 2014 at 1:42 AM, Raphael Bauduin <rblists@gmail.com> wrote:

On Wed, Jan 8, 2014 at 4:05 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Wed, Jan 8, 2014 at 6:37 AM, Raphael Bauduin <rblists@gmail.com> wrote:

Hi

I'm using the json functionalities of postgresql 9.3.
I have a query calling json_populate_recordset like this:
json_populate_recordset(null::product, event->'products')
but it returns an error:
ERROR: cannot call json_populate_recordset on a nested object

There is indeed one key in event->'products' giving access to an array
of
objects.

Is there a way to specify which keys to keep from the object? I haven't
found ti in the docs.

Here is pseudo code of what I'd like to do:
json_populate_recordset(null::product, event->'products' WITH ONLY
KEYS
{'f1','f2'})

unfortunately, not without manipulating the json. this is basically a
somewhat crippling limitation of the json_populate functions -- they
can't handle anything but flat tuples. so you have to do something
highly circuitous.

problem (one record):
postgres=# create table foo(a text, b text);
postgres=# select json_populate_record(null::foo, '{"a": "abc", "b":
"def", "c": [1,2,3]}'::json);
ERROR: cannot call json_populate_record on a nested object

nasty solution:
postgres=# with data as (select '{"a": "abc", "b": "def", "c":
[1,2,3]}'::json as j)
select json_populate_record(null::foo, row_to_json(q)) from
(
select j->'a' as a, j->'b' as b from data
) q;
json_populate_record
----------------------
(abc,def)

with some extra manipulations you can do a record set. basically, you
need to get the json 'right' first (or that can be done on the
client).

ok, thanks for your reply.
Is this considered to be added in the future to the json functions
available? I could use it frequently I think.

At some point the 'populate' functions will be amended so that you
will be able to deserialize to complex row object. For 9.4, if jsonb
makes the cut (which is dicey), that may provide another route -- have
to double check that though.

merlin

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