[
https://issues.apache.org/jira/browse/CALCITE-3139?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Forward Xu updated CALCITE-3139:
--------------------------------
Description:
{{JSON_EXTRACT(_json_doc_}}, _{{path}}_[, path] ...)
Returns data from a JSON document, selected from the parts of the document
matched by the _{{path}}_ arguments. Returns {{NULL}} if any argument is
{{NULL}} or no paths locate a value in the document. An error occurs if the
_{{json_doc}}_ argument is not a valid JSON document or any _{{path}}_ argument
is not a valid path expression.
The return value consists of all values matched by the _{{path}}_ arguments. If
it is possible that those arguments could return multiple values, the matched
values are autowrapped as an array, in the order corresponding to the paths
that produced them. Otherwise, the return value is the single matched value.
Example Sql:
{code:java}
SELECT JSON_EXTRACT(v) AS c1
,JSON_EXTRACT(v, '$.b')) AS c2
,JSON_EXTRACT(v, '$.a[0]')) AS c3
,JSON_EXTRACT(v, '$.a[0]','$.b[0]')) AS c4
FROM (VALUES ('{"a": [10, true],"b": "[11, true]"}')) AS t(v)
limit 10;{code}
Result:
||c1||c2||c3||c4||
|{"a": [10, true],"b": "[10, true]"}|[10, true]|10|[10,11]|
was:
[{{JSON_EXTRACT(_{{json_doc_}}, _{{path}}_[, _\{{path}}_]
...)}}|https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-extract]
Returns data from a JSON document, selected from the parts of the document
matched by the _{{path}}_ arguments. Returns {{NULL}} if any argument is
{{NULL}} or no paths locate a value in the document. An error occurs if the
_{{json_doc}}_ argument is not a valid JSON document or any _{{path}}_ argument
is not a valid path expression.
The return value consists of all values matched by the _{{path}}_ arguments. If
it is possible that those arguments could return multiple values, the matched
values are autowrapped as an array, in the order corresponding to the paths
that produced them. Otherwise, the return value is the single matched value.
Example Sql:
{code:java}
SELECT JSON_EXTRACT(v) AS c1
,JSON_EXTRACT(v, '$.b')) AS c2
,JSON_EXTRACT(v, '$.a[0]')) AS c3
,JSON_EXTRACT(v, '$.a[0]','$.b[0]')) AS c4
FROM (VALUES ('{"a": [10, true],"b": "[11, true]"}')) AS t(v)
limit 10;{code}
Result:
||c1||c2||c3||c4||
|{"a": [10, true],"b": "[10, true]"}|[10, true]|10|[10,11]|
> Implement JSON_EXTRACT function
> -------------------------------
>
> Key: CALCITE-3139
> URL: https://issues.apache.org/jira/browse/CALCITE-3139
> Project: Calcite
> Issue Type: Sub-task
> Reporter: Forward Xu
> Assignee: Forward Xu
> Priority: Major
>
> {{JSON_EXTRACT(_json_doc_}}, _{{path}}_[, path] ...)
> Returns data from a JSON document, selected from the parts of the document
> matched by the _{{path}}_ arguments. Returns {{NULL}} if any argument is
> {{NULL}} or no paths locate a value in the document. An error occurs if the
> _{{json_doc}}_ argument is not a valid JSON document or any _{{path}}_
> argument is not a valid path expression.
> The return value consists of all values matched by the _{{path}}_ arguments.
> If it is possible that those arguments could return multiple values, the
> matched values are autowrapped as an array, in the order corresponding to the
> paths that produced them. Otherwise, the return value is the single matched
> value.
> Example Sql:
> {code:java}
> SELECT JSON_EXTRACT(v) AS c1
> ,JSON_EXTRACT(v, '$.b')) AS c2
> ,JSON_EXTRACT(v, '$.a[0]')) AS c3
> ,JSON_EXTRACT(v, '$.a[0]','$.b[0]')) AS c4
> FROM (VALUES ('{"a": [10, true],"b": "[11, true]"}')) AS t(v)
> limit 10;{code}
> Result:
> ||c1||c2||c3||c4||
> |{"a": [10, true],"b": "[10, true]"}|[10, true]|10|[10,11]|
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
