On Wed, Jan 8, 2014 at 4:05 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
On Wed, Jan 8, 2014 at 6:37 AM, Raphael Bauduin <rblists@xxxxxxxxx> 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
