[CALCITE-2808] Add the JSON_LENGTH function

• Type: Sub-task
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.20.0
• Labels: pull-request-available
• Assignee: Forward Xu
• Reporter: Forward Xu
• Created: 26/Jan/19 11:01
• Updated: 24/Jun/19 15:02
• Resolved: 28/Mar/19 03:59

Description

```
JSON_LENGTH(*json_doc*[, *path*])
```

Returns the length of a JSON document, or, if a path argument is given, the length of the value within the document identified by the path. Returns `NULL` if any argument is `NULL` or the path argument does not identify a value in the document. An error occurs if the json_doc argument is not a valid JSON document or the path argument is not a valid path expression or contains a {*} or }}{{`` wildcard.

The length of a document is determined as follows:

• The length of a scalar is 1.

• The length of an array is the number of array elements.

• The length of an object is the number of object members.

• The length does not count the length of nested arrays or objects.

Example Sql:

// code placeholder
SELECT JSON_LENGTH(v) AS c1
,JSON_LENGTH(v, 'lax $.a') AS c2
,JSON_LENGTH(v, 'strict $.a[0]') AS c3
,JSON_LENGTH(v, 'strict $.a[1]') AS c4
FROM (VALUES ('{"a": [10, true]}')) AS t(v)
LIMIT 10;

Result:

c1	c2	c3	c4
1	2	1	1

 

Issue Links

• links to: GitHub Pull Request #1070