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