gustavodemorais commented on code in PR #28688:
URL: https://github.com/apache/flink/pull/28688#discussion_r3613247629
##########
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:
Also, could we add a test for the $.items[*] wildcard shape we show in the
docs? Right now the docs promise that behavior but there's no coverage for [*]
with JSON_EXISTS
--
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]
