Skip to main content
Takes a JSON document and extracts a JSON scalar value to SQL 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-scalar value is given. For TEXT input, this function pairs with the JSON_EXTRACT function, which doesn’t convert the JSON values to SQL values.

​
Syntax

​
Parameters

​
Return Type

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. Example The following example returns 987654 (unquoted) from JSON_VALUE and "987654" (with quotes) from JSON_POINTER_EXTRACT:

Rows: 1Execution time: 7.72ms

Example The following example casts the extracted value to INT:

Rows: 1Execution time: 5.22ms

Example The following example returns NULL because /value/keywords contains a JSON array, not a scalar:

Rows: 1Execution time: 5.47ms

Example The following example returns NULL for a NULL input:

Rows: 1Execution time: 6.12ms