Re: returning json object with subset of keys

On Wed, Jan 8, 2014 at 6:37 AM, Raphael Bauduin <rblists(at)gmail(dot)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