JSON_VALUE_ARRAY

Reference material for JSON_VALUE_ARRAY function

Takes a JSON document and extracts a JSON array of scalar values to SQL ARRAY(TEXT) value. For JSON strings, removes the outermost quotes and unescapes the values. Other JSON scalars are not changed. Returns a SQL NULL if a non-array is given, or non-scalar value is given (inside the array).

This function pairs with the JSON_EXTRACT function, which doesn’t convert the JSON values to SQL values.

Syntax

JSON_VALUE_ARRAY(<json>)

Parameters

Parameter	Description	Supported input types
<json>	The JSON document.	TEXT

Return Type

ARRAY(TEXT)

• If any of the input is NULL the output is NULL (propagates nulls).

Examples

For the JSON document used in the examples below, see JSON common example. The following example returns NULL because /value/uid contains a JSON string (not an array), and '"987654"' for the raw JSON extract:

SELECT JSON_VALUE_ARRAY(JSON_POINTER_EXTRACT('{"key":123,"value":{"uid":"987654","keywords":["insanely","fast","analytics"]}}', '/value/uid')), JSON_POINTER_EXTRACT('{"key":123,"value":{"uid":"987654","keywords":["insanely","fast","analytics"]}}', '/value/uid');

json_value_array array(text) null	json_pointer_extract text null
NULL	”987654”

Rows: 1Execution time: 5.65ms

The following example returns the keywords array as SQL ARRAY(TEXT):

SELECT JSON_VALUE_ARRAY(JSON_POINTER_EXTRACT('{"value":{"keywords":["insanely","fast","analytics"]}}', '/value/keywords'));

json_value_array array(text null) null
[‘insanely’, ‘fast’, ‘analytics’]

Rows: 1Execution time: 5.89ms

The following example returns NULL for a NULL input:

SELECT JSON_VALUE_ARRAY(NULL);

json_value_array array(text) null
NULL

Rows: 1Execution time: 5.91ms