JSON_VALUE()¶
JSON_VALUE() extracts a scalar value from a JSON document at the specified path and returns it as a string. It is designed for extracting individual values such as strings, numbers, and booleans from a JSON document.
Description¶
JSON_VALUE() navigates a JSON path expression and returns the scalar value found at that location as a SQL string. Unlike JSON_EXTRACT(), which returns the value with its JSON type preserved, JSON_VALUE() always returns a string representation.
If the path does not exist in the document, the function returns NULL. If any argument is NULL, the function returns NULL. Wildcard paths and non-scalar results are not supported.
Syntax¶
> JSON_VALUE(json_doc, path)
Arguments¶
Arguments |
Description |
|---|---|
json_doc |
Required. A JSON document (string, JSON column, or result of a JSON function). |
path |
Required. A JSON path expression pointing to a scalar value. |
Examples¶
DROP DATABASE IF EXISTS dbfuncs;
CREATE DATABASE dbfuncs;
USE dbfuncs;
-- String extraction
SELECT JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname');
SELECT JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.lname');
-- Number extraction (returns string)
SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price');
-- Nested path
SELECT JSON_VALUE('{"a": {"b": [1,2,3]}}', '$.a.b[1]');
-- Boolean value
SELECT JSON_VALUE('{"active": true}', '$.active');
-- NULL JSON value returns SQL NULL
SELECT JSON_VALUE('{"x": null}', '$.x');
-- Missing path returns NULL
SELECT JSON_VALUE('{"a": 1}', '$.missing');
-- NULL input
SELECT JSON_VALUE(NULL, '$.a');
SELECT JSON_VALUE('{"a": 1}', NULL);
-- Via JSON functions
SELECT JSON_VALUE(JSON_OBJECT('k1', 'v1', 'k2', 42), '$.k1');
SELECT JSON_VALUE(JSON_ARRAY(1, 2, 3), '$[0]');
SELECT JSON_VALUE(JSON_ARRAY(1, 2, 3), '$[2]');
-- Table usage
CREATE TABLE t1 (a VARCHAR(200));
INSERT INTO t1 VALUES ('{"name":"Alice","age":30}'), ('{"name":"Bob","age":25}'), (NULL);
SELECT JSON_VALUE(a, '$.name') FROM t1;
SELECT JSON_VALUE(a, '$.age') FROM t1;
DROP TABLE t1;
DROP DATABASE dbfuncs;