4

I need a jsonpath expression, which returns the first element of an array which has "key" property.

I'm looking for the same result as this query:

SELECT
  j
FROM
jsonb_array_elements(
    '[
      {"key": "foo"},
      {"other": "bar"},
      {"key":  "baz", "other": "blah"}
    ]'::JSONB
) j
WHERE
  j ? 'key'
LIMIT 1

My query looks like this currently, but doesn't work

SELECT
    jsonb_path_query(
      '[
        {"key": "foo"},
        {"other": "bar"},
        {"key":  "baz", "other": "blah"}
      ]'::JSONB,
      '$[?(@.key)] [0]')
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
nextstopsun
  • 443
  • 7
  • 14

1 Answers1

1

Please see this: https://www.postgresql.org/docs/12/functions-json.html#FUNCTIONS-SQLJSON-PATH-OPERATORS

SELECT
    jsonb_path_query_first(
      '[
        {"key": "foo"},
        {"other": "bar"},
        {"key":  "baz", "other": "blah"}
      ]'::JSONB,
      '$[*] ? (exists (@.key))')
;

 jsonb_path_query_first 
------------------------
 {"key": "foo"}
(1 row)
Mike Organek
  • 11,647
  • 3
  • 11
  • 26