How to accept any key in jsonb path?

Question

I have a jsonb saved in a PostgreSQL database that has the structure:

{
  "foo": {
    "key0": {
      "bar": "myValueA",
    },
    "key1": {
      "bar": "myValueB",
    }
    // ...
    "keyN": {
      "bar": "myValueN",
    }
  }
}

I would like to get all the records that have the text "myValueX" among all the paths 'foo' > ANY_KEY > 'bar'

It could be 'foo' > 'key0' > 'bar' or 'foo' > 'key1' > 'bar' or any keyN.

In "pseudo" PostgreSQL:

SELECT * FROM "myTable" WHERE "myColumn" #>> '{foo,*,bar}' = 'myValue';

where * would mean "can be any key".

Is there a way to achieve such a query?

Answer 1

It's not pseudo PostgreSQL, it's just PostgreSQL JSONPath. The @@ operator works exactly like that: demo

select * from "myTable" 
where "myColumn" @@ '$.foo.*.bar=="myValueN"';

myColumn
{"foo": {"key0": {"bar": "myValueA"}, "key1": {"bar": "myValueB"}, "keyN": {"bar": "myValueN"}}}

@@ is supported by GIN jsonb_ops and jsonb_path_ops, so you can speed it up with an index:

create index on "myTable" using gin("myColumn");