Good afternoon,
I am running into the following issue with a JSONPath exists query.
This is a valid query
SELECT '{"n": {"a-b": 1, "@ab": 2, "ab": 3}}'::jsonb @? '$ ? (@.n.ab >= 3)';
This is an invalid query (syntax error)
SELECT '{"n": {"a-b": 1, "@ab": 2, "ab": 3}}'::jsonb @? '$ ? (@.n.a-b >= 3)';
The thing that is making it invalid is the key “a-b”. Same error occurs for key
“@ab”. In looking at the following link
https://github.com/postgres/postgres/blob/master/src/include/utils/jsonpath.h#L62,
it looks like anything that is in the enum JsonPathItemType if present in the
query will cause a syntax error and must be escaped like so
SELECT '{"n": {"a-b": 1, "@ab": 2, "ab": 3}}'::jsonb @? '$ ? (@.n.a\-b >= 3)';
I also looked at the section 4.1.4
(https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-SPECIAL-CHARS),
but this is only talking about the SQL allowed/disallowed special characters –
not specific to the JSONPath query.
Looking at the source code here for function printJsonPathItem
https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/jsonpath.c#L685,
I think this is just processing characters in the path one by one, which would
explain why there is no special syntax for how to escape the string.
Question: Is this a valid assumption? If I have a python program (for example)
that is formatting the string for the query '$ ? (@.n.a\-b >= 3)’, is it
correct to format anything that is present in the JsonPathItemType enum
documentation? Of course this assumes all the standard security things about
sanitizing user input and handling the path conversion for arrays correctly –
meaning “a.*.b” must be replaced with “a[*].b”…
If this is documentation I should contribute to, I am happy to – I’d imagine it
belongs in section 9.16.1
https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-PROCESSING
as a footnote to Table 9.46. Additional jsonb Operators
Thanks!
--Vasu
