WL#8607: Inline JSON path expressions in SQL
Status: Complete
This WL adds inlining JSON path expressions in SQL queries to MySQL server. It should allow to execute queries like: SELECT ... FROM t1 WHERE t1.json_field->"$.path.to[0].key"= 123; here 't1.json_field' is the column where specified path is searched, "$.path.to[0].key" is the JSON path expression described in WL#7909, '->' is used as separator between column and string literal containing path. Essentially, this is a syntactic sugar and the query above equivalent to following: SELECT ... FROM t1 WHERE JSN_EXTRACT(t1.json_field, "path.to[0].key") = 123; This WL will do this translation during parsing.
Functional requirements ----------------------- 1) Inlined JSON path expressions should be allowed to appear anywhere a simple expression may appear. Similar applied to where json_* funcs aren't allowed. 2) A query with inlined JSON path expr should produce exactly same result and execution plan as with corresponding json_extract function. Non-functional requirements --------------------------- No bugs/regressions should be introduced.
Outline
-------
This WL aims to allow to inline JSON path expressions into SQL. For example:
SELECT f1->"$.a" FROM t1 WHERE f1->"$.b" = 123 ORDER BY f1->"$.datetime";
here:
f1 is a column identifier, general expressions aren't supported
-> is a separator, could be surrounded by spaces (i.e "f1 -> '$.a'"
is valid expression)
"" is a string literal containing path expression that's acceptable by
JSON_EXTRACT function (see WL#7909). Nothing but string literal is
accepted here. Both ' (single quote) and " (double quote) could be
used to surround string.
It's implemented as pure syntactic sugar and completely resides in parser and
lexer. The query above is equivalent to following:
SELECT json_extract(f1, "$.a") FROM t1
WHERE json_extract(f1, "$.b") = 123 ORDER BY json_extract(f1, "$.datetime");
Due to syntactic sugar nature, all errors, warnings, etc are reported under
name of JSON_EXTRACT function. EXPLAIN will print a call to JSON_EXTRACT which
would have inlined path expression as alias, e.g
f1->"$.a"
will be printed as
json_extract(`test`.`t1`.`f1`,'$.a') AS `f1->"$.a"`
The inlined path expression should be allowed everywhere, where regular call
to JSON_EXTRACT function is allowed. For example (but not limited to):
select list, WHERE/HAVING clauses, ORDER/GROUP BY clauses, etc.
Note on separator
----------------------
As agreed at the optimizer team meeting'15 we'll use '->' as a separator, as
it causes least amount of disagreement.
Changes to parser
-----------------
A rule in parser is extended to create json_extract() function when JSON path
expression is found.
simple_expr:
...
| simple_ident JSON_SEPARATOR_SYM json_path_expr
{
// create function: json_extract(,)
}
Changes to lexer
----------------
Lexer now can additionally return one new token: JSON_SEPARATOR_SYM.
It denotes '->'. This is needed to avoid introducing shift/reduce conflicts in
parser.
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.