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

From Dennis
Subject Re: JSON path wild cards?
Date
Msg-id 8812E2D5-30EF-4C77-80AF-B53B1015CEA9@visi.com
Whole thread Raw
In response to JSON path wild cards?  (Dennis <dennisr@visi.com>)
List pgsql-general
Thanks,  weirdly I just stumbled on the idea when your reply came in, of using jsonb_array_elements function wrapped in
aCTE that uses the containment operator within the CTE to select just the json docs I want and then I can select the
specificjson key values from each jsonb row/object returned by the CTE.  Basically  the same thing as a for each or
wildcard 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} ] } ])
ASae  
)
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
selectiveagainst 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
allthe 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
>



pgsql-general by date:

Previous
From: Michael Paquier
Date:
Subject: Re: JSON path wild cards?
Next
From: Marcin Mańk
Date:
Subject: full_page_writes on SSD?