To address the original question:
... why the null output of jsonb_path_query is omitted?
and the question added in the bounty of tim-mccurrach:
... why the whole row is omitted from the results in the case of a null result.
Either builds on the subtly incorrect assumption that there would be a "null output" or "null result". But, at first, there is really just "no row". Makes all the difference. Related:
jsonb_path_query() is a set-returning function ("SRF"). It doesn't return 1 result, but 0-n result rows.
Standard SQL does not allow SRF in the SELECT list, only in the FROM clause. But Postgres (like some other RDBMS) allows it. Traditionally, that led to odd corner cases. Exact behavior was finally settled (sanitized) with Postgres 10. See:
A single set-returning function like in the example:
SELECT id, jsonb_path_query(...) AS foo FROM example;
is effectively almost1 the same as:
SELECT id, foo FROM example CROSS JOIN LATERAL jsonb_path_query(...) foo;
The (implicit) join eliminates input rows where the SRF comes up empty ("no row"!).
OTOH it multiplies input rows where the SRF produces multiple result rows.
There is an example in the manual explaining this case exactly in the chapter "SQL Functions Returning Sets":
In the last SELECT, notice that no output row appears for Child2,
Child3, etc. This happens because listchildren returns an empty set for those arguments, so no result rows are generated. This is the
same behavior as we got from an inner join to the function result when
using the LATERAL syntax.
PostgreSQL's behavior for a set-returning function in a query's select
list is almost exactly the same as if the set-returning function had
been written in a LATERAL FROM-clause item instead. For example,
SELECT x, generate_series(1,5) AS g FROM tab;
is almost equivalent to
SELECT x, g FROM tab, LATERAL generate_series(1,5) AS g;
Bold emphasis mine.
There is more, including details for "almost"1 above. Read it.
Solution
Working with jsonb directly to trim the noise from casting json.
The original question is oblivious to the fact that multiple objects in the JSON array could qualify.
To get all matches as set like the original query, and also preserve input rows without match, replace the implicit CROSS JOIN with LEFT JOIN ... ON true. (Then "no row" is transformed to null!):
SELECT e.id
, d.val #>> '{}' AS any_value
FROM example e
LEFT JOIN LATERAL jsonb_path_query(e.data, '$[*] ? (@.key == "2").value') d(val) ON true;
See:
Get only the first match, also preserving all input rows:
SELECT id
, jsonb_path_query_first(data, '$[*] ? (@.key == "2").value') #>> '{}' AS first_value
FROM example;
Or get all matches as JSON array, also preserving all input rows:
SELECT id
, jsonb_path_query_array(data, '$[*] ? (@.key == "2").value') AS all_values
FROM example;
fiddle - with added example row that produces two matches to convey the point.
Proper conversion to text
Either of my queries uses the same "trick" to cast the JSON value to text that a_horse already displayed: #>> '{}'. Here's the outstanding explanation:
The JSON #>> operator extracts the JSON sub-object at the specified path as text. The path is provided as Postgres text array text[]. '{}' is an empty array. So we get the root object as text. Voilá. See:
Converting the JSON array to text[] is slightly more sophisticated:
SELECT e.id
, ARRAY(SELECT jsonb_array_elements_text(jsonb_path_query_array(e.data, '$[*] ? (@.key == "2").value'))) AS all_values_txt
, jsonb_array_to_text_array(jsonb_path_query_array(e.data, '$[*] ? (@.key == "2").value')) AS all_values_txt2
FROM example e;
Both convert the JSON array to a Postgres text[]. The second expression encapsulates that in a function as outlined here:
