JSON_LEN scalar function

The JSON_LEN user-defined function returns the number of elements in an element of type array inside a JSON document. NULL is returned if an element is not an array.

Read syntax diagramSkip visual syntax diagramJSON_LEN( injson, inelem)

The schema is SYSTOOLS.

injson
An expression that returns a JSON document in binary format (extended BSON).
inelem
A character or graphic string constant that is not longer than 2048 bytes. inelem contains the path-qualified JSON field name to be searched for.

The result of the function is an integer.

Example

Assume that table T1 contains following JSON document in a column C1 as a BSON format. 

{array: [10,20,30,40]}

Following query returns the number of elements in ‘array’.

SELECT SYSTOOLS.JSON_LEN(C1, 'array') as Length          
FROM T1;
  +----------------+  
  |     LENGTH     |  
  +----------------+  
1_|              4 |  
  +----------------+