[CALCITE-2864] Add the JSON_DEPTH function

• Type: Sub-task
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.19.0
• Labels: pull-request-available
• Assignee: Forward Xu
• Reporter: Forward Xu
• Created: 25/Feb/19 03:29
• Updated: 25/Mar/19 00:11
• Resolved: 28/Feb/19 21:33

Description

Returns the maximum depth of a JSON document. Returns NULL if the argument is NULL. An error occurs if the argument is not a valid JSON document.

An empty array, empty object, or scalar value has depth 1. A nonempty array containing only elements of depth 1 or nonempty object containing only member values of depth 1 has depth 2. Otherwise, a JSON document has depth greater than 2.

 

Example Sql:

SELECT JSON_DEPTH(v) AS c1
,JSON_DEPTH(JSON_VALUE(v, 'lax $.b' ERROR ON ERROR)) AS c2
,JSON_DEPTH(JSON_VALUE(v, 'strict $.a[0]' ERROR ON ERROR)) AS c3
,JSON_DEPTH(JSON_VALUE(v, 'strict $.a[1]' ERROR ON ERROR)) AS c4
FROM (VALUES ('

{"a": [10, true],"b": "[10, true]"}

')) AS t(v)
limit 10;

Result:

c1 c2 c3 c4
== == == == 
3   2   1   1

Issue Links

• links to: GitHub Pull Request #1067