Re: returning json object with subset of keys

On Wed, Jan 8, 2014 at 4:05 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> 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
>

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