From: | Ron Clarke <rclarkeai(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | jsonb_path_query unexpected results when filter key / path does not exist.. |
Date: | 2022-11-16 20:45:26 |
Message-ID: | CAGVf-sNK3omXghNBNnjC1Zq28sXk87uOu+wiJJSMQCXU25fsXA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Currently using postgresql 14. This via pgAdmin 4
If I execute a "jsonb_path_query" with an error in the filter expression, I
get no results from the overall query instead of a null result from
jsonb_path_query...
The example :-
2 json arrays, using array "a" to lookup values in array "b" with a
common code...
This works fine, and gives me the expected result :-
SELECT
j_item as ordinal_num
, jr.j_row as item
, jr.j_row #>> '{a}' as item_code
, jsonb_build_object('x', jr.j_row #>> '{a}') as search_expression
,'[{"b":"A","id":1},{"b":"B","id":2},{"b":"C","id":3}]'::jsonb as item_ids
,
jsonb_path_query('[{"b":"A","id":1},{"b":"B","id":2},{"b":"C","id":3}]'::jsonb,
'$[*] ? (@.b == $x)'::jsonpath, jsonb_build_object('x',
jr.j_row #>> '{a}')
) #>> '{id}' as xid
FROM jsonb_array_elements('[{"a":"A","first":"ann"}
,{"a":"B","first":"bob"}
,{"a":"C","first":"chris"}]'::jsonb) with
ordinality as jr(j_row, j_item);
*Successfully run. Total query runtime: 56 msec.*
*3 rows affected.*
If I swap the filter of the jsonb_path_query expression from (@.b == $x)
to (@.a == $x) the whole select returns nothing and no errors are
reported.
SELECT
j_item as ordinal_num
, jr.j_row as item
, jr.j_row #>> '{a}' as item_code
, jsonb_build_object('x', jr.j_row #>> '{a}') as search_expression
,'[{"b":"A","id":1},{"b":"B","id":2},{"b":"C","id":3}]'::jsonb as item_ids
,
jsonb_path_query('[{"b":"A","id":1},{"b":"B","id":2},{"b":"C","id":3}]'::jsonb,
'$[*] ? (@.a == $x)'::jsonpath, jsonb_build_object('x',
jr.j_row #>> '{a}')
) #>> '{id}' as xid
FROM jsonb_array_elements('[{"a":"A","first":"ann"}
,{"a":"B","first":"bob"}
,{"a":"C","first":"chris"}]'::jsonb) with
ordinality as jr(j_row, j_item);
*Successfully run. Total query runtime: 67 msec. 0 rows affected.*
I appreciate that the filter is looking for an undefined key in the second
example and so I would expect a null column.
I guess this may have something to do with strict and lax options, but I
would have only expected this to affect the column being created by
jsonb_path_query and not to remove the whole result set.
I also think it's likely that I've got something wrong!.
I am trying to do set based stuff rather than looping through results, so I
am expecting to depend upon a null values being returned where appropriate
(i.e. the column returns null but the result still exists for other parts
of the query)...
Thanks for looking at this, and let me know if it's intended behaviour.
Regards
Ron
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2022-11-16 21:02:40 | Re: jsonb_path_query unexpected results when filter key / path does not exist.. |
Previous Message | DeFreitas, Nigel | 2022-11-16 20:30:05 | Possible RegEx bug for lower-case a and s characters |