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

JSON_VALUE(<json>)

​
Parameters

ParameterDescriptionSupported input types
<json>The JSON document.JSON or TEXT

​
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: 
SELECT JSON_VALUE(JSON_POINTER_EXTRACT('{"key":123,"value":{"uid":"987654"}}', '/value/uid')), JSON_POINTER_EXTRACT('{"key":123,"value":{"uid":"987654"}}', '/value/uid');
json_value text nulljson_pointer_extract text null
987654”987654”

Rows: 1Execution time: 7.72ms

Example The following example casts the extracted value to INT: 
SELECT JSON_VALUE(JSON_POINTER_EXTRACT('{"key":123,"value":{"uid":"987654"}}', '/key'))::INT;
json_value int null
123

Rows: 1Execution time: 5.22ms

Example The following example returns NULL because /value/keywords contains a JSON array, not a scalar: 
SELECT JSON_VALUE(JSON_POINTER_EXTRACT('{"value":{"keywords":["insanely","fast","analytics"]}}', '/value/keywords'));
json_value text null
NULL

Rows: 1Execution time: 5.47ms

Example The following example returns NULL for a NULL input: 
SELECT JSON_VALUE(NULL);
json_value text null
NULL

Rows: 1Execution time: 6.12ms