Thread: returning json object with subset of keys
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'})
Thx
--
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.org event->'products'
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'})
Thx
--
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.org event->'products'
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
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:unfortunately, not without manipulating the json. this is basically a
> 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'})
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