JSON path wild cards?

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

Thanks, weirdly I just stumbled on the idea when your reply came in, of using jsonb_array_elements function wrapped in a CTE that uses the containment operator within the CTE to select just the json docs I want and then I can select the specific json key values from each jsonb row/object returned by the CTE. Basically the same thing as a for each or wild card and it’s super fast with the jsonb indexing.

e.g

WITH jsd AS
(
SELECT jsonb_array_elements([ {“a”:1,”b”: [ { “x”: 7,”y”:8,”z”:9} ] }, {“a”:2,”b”: [ { “x”: 4,”y”:5,”z”:6} ] } ]) AS ae
)
SELECT ae->’b’ FROM jsd;

Note: In my real world problem I have WHERE clause in the CTW statement using the @> operator so the CTE can be more selective against the entire set of jsonb doc’s in the containing table.

On Nov 23, 2015, at 10:39 PM, Dennis <dennisr@visi.com> wrote:

Is there a way to specify a wild card in a json path?

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Nov 24, 2015 at 12:39 PM, Dennis <dennisr@visi.com> wrote:

Is there a way to specify a wild card in a json path?

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

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

Not in postgres currently, but you may try our jsquery extension
https://github.com/postgrespro/jsquery.

Oleg