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?
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') #>> '{}