Skip to main content
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

ParameterDescriptionSupported 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) nulljson_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