gustavodemorais commented on code in PR #28688:
URL: https://github.com/apache/flink/pull/28688#discussion_r3613165405
##########
docs/data/sql_functions.yml:
##########
@@ -1223,6 +1218,60 @@ 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')
+
+
+ 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.
+
+ Nested arrays and objects each count as a single element and their
contents are not included in the count.
+
+ Lax and strict mode produce the same output, so mode selection is
disabled for this function.
+
+ 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]`.
+
+ When provided with a path that uses a wildcard and resolves in 2 or more
paths, JSON_LENGTH will resolve as NULL.
+
+ Because a NULL result can mean several different things (the input is
not valid JSON, the path
+ does not match anything, or a wildcard path matched 2 or more nodes), it
is recommended to pair
+ JSON_LENGTH with a helper function so invalid input is handled
explicitly rather than silently
+ returning NULL:
+
+ - Without a path, guard the call with IS JSON to separate malformed
input from a real result:
+
+ -- returns the length only for valid JSON, otherwise NULL means
"invalid input"
+ SELECT CASE WHEN json_doc IS JSON THEN JSON_LENGTH(json_doc) END;
+
+ - With a path, use JSON_EXISTS to tell "the path is absent" apart from
"the path matched but
+ was ambiguous / matched 2 or more nodes":
+
+ -- path_present is TRUE even when JSON_LENGTH is NULL because of a
multi-match wildcard
+ SELECT JSON_EXISTS(json_doc, '$.items[*]'), JSON_LENGTH(json_doc,
'$.items[*]');
Review Comment:
This part of a bit too wordy. We can convey the same information in a more
concise way. Suggestion:
```suggestion
```suggestion
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));
```
```
This also corrects us overselling json_exists. Added one example on how to
count multiple matches using JSON_QUERY. Could you add tests for the examples
to make sure we're adding runnable examples?
--
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]
