Re: JSON path wild cards? - Mailing list pgsql-general

From Michael Paquier
Subject Re: JSON path wild cards?
Date
Msg-id CAB7nPqQAY+vO3GdeP6GLFZSCrvitdFaL-BKiWvPLXa431tR67w@mail.gmail.com
Whole thread Raw
In response to JSON path wild cards?  (Dennis <dennisr@visi.com>)
List pgsql-general


On Tue, Nov 24, 2015 at 1:39 PM, Dennis <dennisr@visi.com> wrote:
> Is there a way to specify a wild card in a json path?

No.

> For example I have the following json doc:
>
> [ {“a”:1,”b”: [ { “x”: 7,”y”:8,”z”:9} ] },  {“a”:2,”b”: [ { “x”: 4,”y”:5,”z”:6} ] }, … ]
>
> How do I write a select clause that can return the values for all b x values something like [{b:x}] that would return all the b:x values in the array? e.g. 7 and 4 ...

To do a lookup at json arrays and look at what you wish you are going to need some logic based on json_array_elements with -> or ->>. For example using your case above:
=#  select ((value->'b')::json)->0->'x' as keys
    from json_array_elements('[ {"a":1,"b": [ { "x": 7,"y":8,"z":9} ] },{"a":2,"b": [ { "x": 4,"y":5,"z":6} ] }]'::json) AS json_data;
 keys
------
 7
 4
(2 rows)

That's a bit rough I agree but the correct functions wrapped with some plpgsql or SQL could prove to be generic enough.

> Also is there a definition of the syntax of a proper json path for use in postgres?

http://www.postgresql.org/docs/devel/static/functions-json.html
--
Michael

pgsql-general by date:

Previous
From: Dennis
Date:
Subject: JSON path wild cards?
Next
From: Dennis
Date:
Subject: Re: JSON path wild cards?