Обсуждение: 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

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