sqlpostgresqljsonbjsonpathset-returning-functions

PostgreSQL jsonb_path_query removes result instead of returning null value


In an example table:

CREATE TABLE example (
    id   SERIAL PRIMARY KEY,
    data JSON NOT NULL );
INSERT INTO example (id, data) VALUES
  (1, '[{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}]')
, (2, '[{"key": "1", "value": "val1"}]')
, (3, '[{"key": "1", "value": "val1"}, {"key": "2", "value": "val2"}]');

I want to query the value field in the data column where key = 2. The query I'm currently using is this:

SELECT id,
       jsonb_path_query(
               TO_JSONB(data),
               '$[*] ? (@.key == "2").value'::JSONPATH
           )::VARCHAR AS values
FROM example

I would expect the results to be:

id values
1 "val2"
2 null
3 "val2"

But the actual result is:

id values
1 "val2"
3 "val2"

Is there a reason why the null output of jsonb_path_query() is omitted?
How do I get it to behave the way I'm expecting?


Solution

  • You want jsonb_path_query_first() if you want the result of the path expression:

    SELECT id,
           jsonb_path_query_first(data, '$[*] ? (@.key == "2").value') AS values
    FROM example
    

    Note that this returns a jsonb value. If you want a text value, use:

    jsonb_path_query_first(data, '$[*] ? (@.key == "2").value') #>> '{}