WL#9124: Extension of inlined JSON path operator
Affects: Server-8.0
—
Status: Complete
WL#8607 Inlined JSON path introduced a new operator "->" that is a shortcut for JSON_EXTRACT function. However it turned out to be not very convenient to use it with string data, as it requires unquoting. Goal of this WL is to address that by adding yet another operator "->>", which is a shortcut for JSON_UNQUOTE(JSON_EXTRACT(...)). Example: json_field->>"$.a_key" is same as JSON_UNQUOTE(JSON_EXTRACT(json_field, "$.a_key")) "->>" is chosen because of Postgres, which has this operator with same meaning. This WL is pure syntactic sugar, exactly like WL#8607. This also fixes MySQL BUG#78736.
Functional requirements ----------------------- 1) Inlined JSON path operator ->> 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_unquote(json_extract()) functions call. Non-functional requirements --------------------------- No bugs/regressions should be introduced.
Outline ------- This WL aims to allow to inline unquoting JSON path expressions into SQL. For example: SELECT f1->>"$.a" FROM t1 WHERE f1->>"$.b" = "asd" 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_unquote(json_extract(f1, "$.a")) FROM t1 WHERE json_unquote(json_extract(f1, "$.b")) = "asd" ORDER BY json_unquote(json_extract(f1, "$.datetime")); Due to syntactic sugar nature, all errors, warnings, etc are reported under name of JSON_UNQUOTE(JSON_EXTRACT()) functions. Traditional EXPLAIN in warning will print a call to JSON_UNQUOTE(JSON_EXTRACT()) which would have inlined path expression as alias, e.g f1->>"$.a" will be printed as json_unquote(json_extract(`test`.`t1`.`f1`,'$.a')) AS `f1->>"$.a"` Same applies to condition printout in structured EXPLAIN. The inlined path expression should be allowed everywhere, where regular call to JSON_UNQUOTE(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 we'll use '->>' for unquoting operator, as Postgres has this operator with same meaning.
Changes to parser ----------------- A rule in parser is extended to create json_unquote(json_extract()) functions when JSON path expression is found. simple_expr: ... | simple_ident JSON_UNQUOTED_SEPARATOR_SYM json_path_expr { // create function: // jspon_unquote(json_extract(, )) } Changes to lexer ---------------- Lexer now can additionally return one new token: JSON_UNQUOTED_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.