Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). 

# JSON\$1ARRAY\$1LENGTH function
<a name="JSON_ARRAY_LENGTH"></a>

**Note**  
JSON\$1PARSE and its associated functions parse JSON values as SUPER, which Amazon Redshift parses more efficiently than VARCHAR.  
 Instead of using JSON\$1ARRAY\$1LENGTH, we recommend that you parse your JSON strings using the [JSON\$1PARSE function](JSON_PARSE.md) to get a SUPER value. Then, use the [GET\$1ARRAY\$1LENGTH function](get_array_length.md) to get the length of your array. 

The JSON\$1ARRAY\$1LENGTH function returns the number of elements in the outer array of a JSON string. If the *null\$1if\$1invalid* argument is set to `true` and the JSON string is invalid, the function returns `NULL` instead of returning an error.

For more information, see [JSON functions](json-functions.md). 

## Syntax
<a name="JSON_ARRAY_LENGTH-synopsis"></a>

```
JSON_ARRAY_LENGTH('json_array' [, null_if_invalid ] ) 
```

## Arguments
<a name="JSON_ARRAY_LENGTH-arguments"></a>

 *json\$1array*  
A properly formatted JSON array.

 *null\$1if\$1invalid*  
(Optional) A `BOOLEAN` value that specifies whether to return `NULL` if the input JSON string is invalid instead of returning an error. To return `NULL` if the JSON is invalid, specify `true` (`t`). To return an error if the JSON is invalid, specify `false` (`f`). The default is `false`.

## Return type
<a name="JSON_ARRAY_LENGTH-return"></a>

`INTEGER`

## Examples
<a name="JSON_ARRAY_LENGTH-examples"></a>

To return the number of elements in the array, use the following example. 

```
SELECT JSON_ARRAY_LENGTH('[11,12,13,{"f1":21,"f2":[25,26]},14]'); 

+-------------------+
| json_array_length |
+-------------------+
|                 5 |
+-------------------+
```

To return an error because the JSON is invalid, use the following example.

```
SELECT JSON_ARRAY_LENGTH('[11,12,13,{"f1":21,"f2":[25,26]},14');
 
ERROR: invalid json array object [11,12,13,{"f1":21,"f2":[25,26]},14
```

To set *null\$1if\$1invalid* to *true*, so the statement the returns `NULL` instead of returning an error for invalid JSON, use the following example.

```
SELECT JSON_ARRAY_LENGTH('[11,12,13,{"f1":21,"f2":[25,26]},14',true);

+-------------------+
| json_array_length |
+-------------------+
| NULL              |
+-------------------+
```