JSON_VALUE

​

Syntax

​

Json value plain (scalar)

json_value(json_string, path)

• Takes two arguments, JSON_VALUE(json_string, path), and returns STRING. ON EMPTY, ON ERROR, PASSING, and other RETURNING types are outside this alias’s supported forms.

​

Arguments

​

Returns

• Type: STRING
• NULL behavior: NULL inputs or a missing or invalid path return NULL. Objects and arrays return NULL. A JSON null scalar returns the text null.

​

Properties

​

Units

• Input unit: Array path indices are zero based.
• Output unit: Text

​

Usage notes

• Array indices are zero based.
• JSON strings are unquoted; numbers and Booleans become their textual representations.
• JSON null handling here differs from GET_JSON_OBJECT.
• JSON_VALUE uses the same extraction rules as JSON_EXTRACT_SCALAR and returns STRING. Use its two-argument form.
• Deterministic: The same inputs and settings produce the same result.

​

Examples

​

JSON_VALUE default STRING form

• Takes two arguments, JSON_VALUE(json_string, path), and returns STRING. ON EMPTY, ON ERROR, PASSING, and other RETURNING types are outside this alias’s supported forms.

SELECT json_value('{"a":7}', '$.a') AS "value";

​

Read an array element and a missing field

SELECT json_value('{"items":[10,20]}', '$.items[1]') AS second_item, json_value('{"items":[10,20]}', '$.missing') AS missing;

​

Limitations

• Use the path syntax and extraction forms shown on this page.
• JSON_VALUE takes two arguments and returns STRING. ON EMPTY, ON ERROR, and PASSING clauses are not supported.
• A RETURNING clause does not change the STRING result type. Do not use it to request a typed result.

​

Related functions

• json_extract
• get_json_object