Re: JSON path wild cards?

On Tue, Nov 24, 2015 at 1:39 PM, Dennis <dennisr(at)visi(dot)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