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, 2024, Oracle Corporation and/or its affiliates. All rights reserved.