PostgreSQL json path expression to find first array element having a specific key

Question

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]')

Mike Organek · Accepted Answer · 2020-07-01 11:25:35Z

2

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)

edited Jul 1, 2020 at 11:25

answered Jul 1, 2020 at 11:20

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bsplosion Mar 21 at 17:37

To help with search, if you're writing any json path query which is guaranteed to return one result (e.g. '$[0]' accessing the first position of an array), leveraging jsonb_path_query_first instead of jsonb_path_query is a great way to work around set-returning function limitations. This lets you use json path queries in WHERE clauses, window functions, etc which otherwise would break with the set return value.

Collectives™ on Stack Overflow

PostgreSQL json path expression to find first array element having a specific key

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related