{"id":18349,"date":"2021-07-06T07:28:36","date_gmt":"2021-07-05T21:28:36","guid":{"rendered":"https:\/\/database.guide\/?p=18349"},"modified":"2021-07-06T08:39:21","modified_gmt":"2021-07-05T22:39:21","slug":"mariadb-json_value-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/mariadb-json_value-explained\/","title":{"rendered":"MariaDB JSON_VALUE() Explained"},"content":{"rendered":"\n

In MariaDB, JSON_VALUE()<\/code> is a built-in function that returns a scalar value from a JSON document. More specifically, it returns the scalar specified by the path provided.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The syntax goes like this:<\/p>\n\n\n\n

JSON_VALUE(json_doc, path)<\/code><\/pre>\n\n\n\n
Where json_doc<\/code> is the JSON document, and path<\/code> is a path within the document. <\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s an example to demonstrate.<\/p>\n\n\n\n
SET @json_document = '\n    { \n        \"name\": \"Wag\", \n        \"type\": \"Dog\", \n        \"weight\": 20 \n    }\n';\nSELECT JSON_VALUE(@json_document, '$.name');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------------------------------------+\n| JSON_VALUE(@json_document, '$.name') |\n+--------------------------------------+\n| Wag                                  |\n+--------------------------------------+<\/pre>\n\n\n\n
Non-Existent Path<\/h2>\n\n\n\n
Passing a path that doesn’t exist in the JSON document results in NULL<\/code>.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
SET @json_document = '\n    { \n        \"name\": \"Wag\", \n        \"type\": \"Dog\", \n        \"weight\": 20 \n    }\n';\nSELECT JSON_VALUE(@json_document, '$.color');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+---------------------------------------+\n| JSON_VALUE(@json_document, '$.color') |\n+---------------------------------------+\n| NULL                                  |\n+---------------------------------------+<\/pre>\n\n\n\n
Arrays<\/h2>\n\n\n\n
Here’s an example of returning data from an array:<\/p>\n\n\n\n
SET @json_document = '\n    { \n        \"_id\" : 1, \n        \"awards\" : [ \"Top Dog\", \"Best Dog\", \"Biggest Dog\" ] \n    }\n';\nSELECT JSON_VALUE(@json_document, '$.awards[1]');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------------------------------------+\n| JSON_VALUE(@json_document, '$.awards[1]') |\n+-------------------------------------------+\n| Best Dog                                  |\n+-------------------------------------------+<\/pre>\n\n\n\n
Arrays are zero-based, and so $.awards[1]<\/code> extracts the second element of the awards<\/code> array.<\/p>\n\n\n\n
Nested Objects<\/h2>\n\n\n\n
Here’s an example of getting a value from an object nested inside another object:<\/p>\n\n\n\n
SET @json_document = '\n    { \n        \"_id\" : 1, \n        \"name\" : \"Wag\", \n        \"details\" : {\n            \"type\" : \"Dog\", \n            \"weight\" : 20,\n            \"awards\" : { \n                \"Florida Dog Awards\" : \"Top Dog\", \n                \"New York Marathon\" : \"Fastest Dog\", \n                \"Sumo 2020\" : \"Biggest Dog\"\n            }\n        }\n    }\n';\nSELECT JSON_VALUE(\n    @json_document, \n    '$.details.awards.Florida Dog Awards'\n    ) AS Result;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+---------+\n| Result  |\n+---------+\n| Top Dog |\n+---------+<\/pre>\n\n\n\n
Nonscalar Values<\/h2>\n\n\n\n
Attempting to return a nonscalar value (e.g. an object or an array) returns NULL<\/code>.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
SET @json_document = '\n    { \n        \"_id\" : 1, \n        \"name\" : \"Wag\", \n        \"details\" : {\n            \"type\" : \"Dog\", \n            \"weight\" : 20,\n            \"awards\" : { \n                \"Florida Dog Awards\" : \"Top Dog\", \n                \"New York Marathon\" : \"Fastest Dog\", \n                \"Sumo 2020\" : \"Biggest Dog\"\n            }\n        }\n    }\n';\nSELECT JSON_VALUE(\n    @json_document, \n    '$.details.awards'\n    ) AS Result;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------+\n| Result |\n+--------+\n| NULL   |\n+--------+<\/pre>\n\n\n\n
To return a nonscalar value, use the JSON_QUERY()<\/a><\/code> function or the JSON_EXTRACT()<\/a><\/code> function.<\/p>\n\n\n\n
Null Arguments<\/h2>\n\n\n\n
If any argument is NULL<\/code>, the result is NULL<\/code>:<\/p>\n\n\n\n
SELECT \n    JSON_VALUE(null, '$.type'),\n    JSON_VALUE('{\"a\":1}', null);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+----------------------------+-----------------------------+\n| JSON_VALUE(null, '$.type') | JSON_VALUE('{\"a\":1}', null) |\n+----------------------------+-----------------------------+\n| NULL                       | NULL                        |\n+----------------------------+-----------------------------+<\/pre>\n\n\n\n
Incorrect Parameter Count<\/h2>\n\n\n\n
Providing no arguments results in an error:<\/p>\n\n\n\n
SELECT JSON_VALUE();<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_VALUE'<\/pre>\n\n\n\n
It’s the same when you provide too few or too many arguments:<\/p>\n\n\n\n
SELECT JSON_VALUE('{ \"a\": 1}');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_VALUE'<\/pre>\n","protected":false},"excerpt":{"rendered":"
In MariaDB, JSON_VALUE() is a built-in function that returns a scalar value from a JSON document. More specifically, it returns the scalar specified by the path provided.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[117],"tags":[93,80,20],"class_list":["post-18349","post","type-post","status-publish","format-standard","hentry","category-mariadb","tag-functions","tag-json","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18349","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=18349"}],"version-history":[{"count":7,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18349\/revisions"}],"predecessor-version":[{"id":18426,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18349\/revisions\/18426"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18349"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18349"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18349"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}