gustavodemorais commented on code in PR #28688:
URL: https://github.com/apache/flink/pull/28688#discussion_r3614447159
##########
docs/data/sql_functions.yml:
##########
@@ -1223,7 +1218,71 @@ json:
-- [{"nested_json":{"value":42}}]
JSON_ARRAY(JSON('{"nested_json": {"value": 42}}'))
```
-
+ - sql: JSON_LENGTH(json_doc[, path])
+ table: jsonLength(jsonObject[, path])
+ description: |
+ Returns the number of elements in a JSON document, or the length of the
value at the specified path if one is provided.
+ Returns NULL if the argument is NULL, the json is invalid, or the path
does not locate a value.
+ eg.
+ -- 2
+ JSON_LENGTH('{"1": "hello", "2": "bye bye"}')
+
+ -- 5
+ JSON_LENGTH('[1,2,3,4,5]')
+
+ -- 1
+ JSON_LENGTH('"hello"')
+
+ -- 1
+ JSON_LENGTH('{"1": "hello", "2": "bye bye"}', '$.2')
+
+ -- NULL
+ JSON_LENGTH('{"1": "hello", "2": "BAD SYNTAX ->"', '$.2')
+
+
+ The length is determined as follows:
+
+ - Scalar values (number, string, boolean): has length 1.
+ - Array: has a length equal to the number of its elements.
+ - Object: has a length equal to the number of its key-value pairs.
+
+ The path argument uses the form:
+
+ path ::= '$' ( '.' `field` | '[' `index` ']' )*
+ field ::= a key in a JSON object
+ index ::= a zero-based position in a JSON array
+
+ For example: `$.author.address` or `$.metadata.tags[0]`.
+
+ A wildcard path that matches 2 or more nodes returns NULL.
+ A NULL result is ambiguous - it means invalid JSON, no match, or a
multi-match wildcard.
+
+ Pair JSON_LENGTH with a helper function to handle these cases explicitly:
+ -- IS JSON separates invalid input from a real result
+ SELECT CASE WHEN json_doc IS JSON THEN JSON_LENGTH(json_doc) END;
+
+ -- JSON_EXISTS separates an absent path from a present one
+ SELECT JSON_EXISTS(json_doc, '$.items[*]'), JSON_LENGTH(json_doc,
'$.items[*]');
+
+ -- To count multiple wildcard matches, wrap them into an array first
+ SELECT JSON_LENGTH(JSON_QUERY(json_doc, '$.items[*]' WITH ARRAY
WRAPPER));
+
Review Comment:
This actually doesn't work, let's just drop this. The whole multiple
wildcard matches thing is overcomplicated and not the focus of the feature
##########
docs/data/sql_functions.yml:
##########
@@ -1223,7 +1218,71 @@ json:
-- [{"nested_json":{"value":42}}]
JSON_ARRAY(JSON('{"nested_json": {"value": 42}}'))
```
-
+ - sql: JSON_LENGTH(json_doc[, path])
+ table: jsonLength(jsonObject[, path])
+ description: |
+ Returns the number of elements in a JSON document, or the length of the
value at the specified path if one is provided.
+ Returns NULL if the argument is NULL, the json is invalid, or the path
does not locate a value.
+ eg.
+ -- 2
+ JSON_LENGTH('{"1": "hello", "2": "bye bye"}')
+
+ -- 5
+ JSON_LENGTH('[1,2,3,4,5]')
+
+ -- 1
+ JSON_LENGTH('"hello"')
+
+ -- 1
+ JSON_LENGTH('{"1": "hello", "2": "bye bye"}', '$.2')
+
+ -- NULL
+ JSON_LENGTH('{"1": "hello", "2": "BAD SYNTAX ->"', '$.2')
+
+
+ The length is determined as follows:
+
+ - Scalar values (number, string, boolean): has length 1.
+ - Array: has a length equal to the number of its elements.
+ - Object: has a length equal to the number of its key-value pairs.
+
+ The path argument uses the form:
+
+ path ::= '$' ( '.' `field` | '[' `index` ']' )*
+ field ::= a key in a JSON object
+ index ::= a zero-based position in a JSON array
+
+ For example: `$.author.address` or `$.metadata.tags[0]`.
+
+ A wildcard path that matches 2 or more nodes returns NULL.
+ A NULL result is ambiguous - it means invalid JSON, no match, or a
multi-match wildcard.
+
+ Pair JSON_LENGTH with a helper function to handle these cases explicitly:
+ -- IS JSON separates invalid input from a real result
+ SELECT CASE WHEN json_doc IS JSON THEN JSON_LENGTH(json_doc) END;
+
+ -- JSON_EXISTS separates an absent path from a present one
+ SELECT JSON_EXISTS(json_doc, '$.items[*]'), JSON_LENGTH(json_doc,
'$.items[*]');
+
+ -- To count multiple wildcard matches, wrap them into an array first
+ SELECT JSON_LENGTH(JSON_QUERY(json_doc, '$.items[*]' WITH ARRAY
WRAPPER));
+
+
+ eg.
+ consider this JSON_LENGTH query JSON_LENGTH('{"1": "hello", "2": "BAD
SYNTAX ->"', '$.2'),
+ this string will return NULL, however as a user, to understand why we
must use the parameter,
+ "IS JSON" and function "JSON_EXISTS()".
+
+ In order to understand why JSON_LENGTH gave a NULL output you can look
at the following example:
+
+ -- '{"1": "hello", "2": "BAD SYNTAX ->"' IS JSON
+ This will return FALSE if we have a malformed JSON input.
+
+ -- JSON_EXISTS('{"1": "hello", "2": "BAD SYNTAX ->"', '$.2')
+ This will return FALSE if the path given is an invalid path in our
given JSON string or
+ invalid JSON, so you must do "IS JSON" first to get a definative
answer. However if this
+ returns TRUE and the JSON input is validated already, it means that
the path describes
+ two or more paths (ie. using the wildcard operator), which
JSON_LENGTH doesnt support.
Review Comment:
```suggestion
```
This is information specifically about the other built in functions and out
of place here. Looks like AI overexplaining some specific use case. I'd say
delete
##########
docs/data/sql_functions.yml:
##########
@@ -1223,7 +1218,71 @@ json:
-- [{"nested_json":{"value":42}}]
JSON_ARRAY(JSON('{"nested_json": {"value": 42}}'))
```
-
+ - sql: JSON_LENGTH(json_doc[, path])
+ table: jsonLength(jsonObject[, path])
+ description: |
+ Returns the number of elements in a JSON document, or the length of the
value at the specified path if one is provided.
+ Returns NULL if the argument is NULL, the json is invalid, or the path
does not locate a value.
+ eg.
+ -- 2
+ JSON_LENGTH('{"1": "hello", "2": "bye bye"}')
+
+ -- 5
+ JSON_LENGTH('[1,2,3,4,5]')
+
+ -- 1
+ JSON_LENGTH('"hello"')
+
+ -- 1
+ JSON_LENGTH('{"1": "hello", "2": "bye bye"}', '$.2')
+
+ -- NULL
+ JSON_LENGTH('{"1": "hello", "2": "BAD SYNTAX ->"', '$.2')
+
+
+ The length is determined as follows:
+
+ - Scalar values (number, string, boolean): has length 1.
+ - Array: has a length equal to the number of its elements.
+ - Object: has a length equal to the number of its key-value pairs.
+
+ The path argument uses the form:
+
+ path ::= '$' ( '.' `field` | '[' `index` ']' )*
+ field ::= a key in a JSON object
+ index ::= a zero-based position in a JSON array
+
+ For example: `$.author.address` or `$.metadata.tags[0]`.
+
+ A wildcard path that matches 2 or more nodes returns NULL.
+ A NULL result is ambiguous - it means invalid JSON, no match, or a
multi-match wildcard.
+
+ Pair JSON_LENGTH with a helper function to handle these cases explicitly:
+ -- IS JSON separates invalid input from a real result
+ SELECT CASE WHEN json_doc IS JSON THEN JSON_LENGTH(json_doc) END;
+
+ -- JSON_EXISTS separates an absent path from a present one
+ SELECT JSON_EXISTS(json_doc, '$.items[*]'), JSON_LENGTH(json_doc,
'$.items[*]');
Review Comment:
I think a test for json_exists with wildcard is still missing
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
