postgres jsonb query with array wildcard like jsonpath: "$.data.text1[?(@.lang=="de")]"

Question

I need to query elements inside a jsonb object array in postgres.

"data": { 
   "text1": [
      {
        "lang": "de",
        "property1": "abc",
        "property2": "def"
      },
      {
        "lang": "en",
        "property1": "ghi",
        "property2": "jkl"
      }
   ],
   "text2": [
      {
        "lang": "de",
        "property1": "mno",
        "property2": "pqr"
      },
      {
        "lang": "en",
        "property1": "stu",
        "property2": "vwx"
      }
   ]
}

Is its possible to select fields within a where clause in the style of jsonpath:

$.data.text1[?(@.lang=="de")]

For example:

select id, json from xyz where json->'data'->'text1'->[?(@.lang=="de")]->property1 is not null

It should be possible to query more than one property like :

select id, json from xyz where json->'data'->'text1'->[?(@.lang=="de")]->property1 is not null and json->'data'->'text2'->[?(@.lang=="de")]->property2 = "pqr"

Answer 1

It's possible with a JSON path expression, e.g.:

select *
from the_table
where the_column @@ '$.data[*].** ? (@.lang == "de").property2 == "pqr"'

or

select *
from the_table
where the_column @@ '$.data[*].** ? (@.lang == "de").property1 <> null'