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

​
Parameters

​
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:

Rows: 1Execution time: 5.65ms

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

Rows: 1Execution time: 5.89ms

The following example returns NULL for a NULL input:

Rows: 1Execution time: 5.91ms