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