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 <[email protected]> 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 ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
