{"id":18342,"date":"2021-07-04T08:38:55","date_gmt":"2021-07-03T22:38:55","guid":{"rendered":"https:\/\/database.guide\/?p=18342"},"modified":"2021-07-04T08:38:55","modified_gmt":"2021-07-03T22:38:55","slug":"mariadb-json_extract-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/mariadb-json_extract-explained\/","title":{"rendered":"MariaDB JSON_EXTRACT() Explained"},"content":{"rendered":"\n

In MariaDB, JSON_EXTRACT()<\/code> is a built-in function that extracts data from a JSON document, based on a given path or paths.<\/p>\n\n\n\n

It can return single values and multiple values. If a single value is matched, a single value is returned. If multiple values are matched, then those values are returned in an array.<\/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_EXTRACT(json_doc, path[, path] ...)<\/code><\/pre>\n\n\n\n
Where json_doc<\/code> is the JSON document, and each path<\/code> argument 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_EXTRACT(@json_document, '$.name');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+----------------------------------------+\n| JSON_EXTRACT(@json_document, '$.name') |\n+----------------------------------------+\n| \"Wag\"                                  |\n+----------------------------------------+<\/pre>\n\n\n\n
Multiple Paths<\/h2>\n\n\n\n
Here’s an example of specifying multiple paths in order to return multiple values from the JSON document. <\/p>\n\n\n\n
When you return multiple values, they are returned in an array.<\/p>\n\n\n\n
SET @json_document = '\n    { \n        \"name\": \"Wag\", \n        \"type\": \"Dog\", \n        \"weight\": 20 \n    }\n';\nSELECT JSON_EXTRACT(@json_document, '$.name', '$.weight');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+----------------------------------------------------+\n| JSON_EXTRACT(@json_document, '$.name', '$.weight') |\n+----------------------------------------------------+\n| [\"Wag\", 20]                                        |\n+----------------------------------------------------+<\/pre>\n\n\n\n
Non-Existent Paths<\/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_EXTRACT(@json_document, '$.color');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------------------------------------+\n| JSON_EXTRACT(@json_document, '$.color') |\n+-----------------------------------------+\n| NULL                                    |\n+-----------------------------------------+<\/pre>\n\n\n\n
However, if multiple paths are passed, and at least one of them matches, then the matching value is extracted and returned in an array. This is true even if only one value is extracted.<\/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_EXTRACT(@json_document, '$.name', '$.color');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+---------------------------------------------------+\n| JSON_EXTRACT(@json_document, '$.name', '$.color') |\n+---------------------------------------------------+\n| [\"Wag\"]                                           |\n+---------------------------------------------------+<\/pre>\n\n\n\n
Arrays<\/h2>\n\n\n\n
Here’s an example of extracting 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_EXTRACT(@json_document, '$.awards[0]');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+---------------------------------------------+\n| JSON_EXTRACT(@json_document, '$.awards[0]') |\n+---------------------------------------------+\n| \"Top Dog\"                                   |\n+---------------------------------------------+<\/pre>\n\n\n\n
Arrays are zero-based, and so $.awards[0]<\/code> extracts the first element of the awards<\/code> array.<\/p>\n\n\n\n
Nested Objects<\/h2>\n\n\n\n
Here’s an example of extracting data 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_EXTRACT(\n    @json_document, \n    '$.details.awards.New York Marathon'\n    ) AS Result;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+---------------+\n| Result        |\n+---------------+\n| \"Fastest Dog\" |\n+---------------+<\/pre>\n\n\n\n
However, if we wanted to extract all awards, we could shorten the path to $.details.awards<\/code>:<\/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_EXTRACT(\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| {\"Florida Dog Awards\": \"Top Dog\", \"New York Marathon\": \"Fastest Dog\", \"Sumo 2020\": \"Biggest Dog\"} |\n+---------------------------------------------------------------------------------------------------+<\/pre>\n\n\n\n
Prettify the Result<\/h2>\n\n\n\n
We can make the result easier to read by passing JSON_EXTRACT()<\/code> to the JSON_DETAILED()<\/a><\/code> function:<\/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 \n    JSON_DETAILED(\n        JSON_EXTRACT(\n            @json_document, \n            '$.details.awards'\n            ) \n    ) AS Result;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------------------------------------+\n| Result                                         |\n+------------------------------------------------+\n| {\n    \"Florida Dog Awards\": \"Top Dog\",\n    \"New York Marathon\": \"Fastest Dog\",\n    \"Sumo 2020\": \"Biggest Dog\"\n} |\n+------------------------------------------------+<\/pre>\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_EXTRACT(null, '$.type'),\n    JSON_EXTRACT('{\"a\":1}', null);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------------------+-------------------------------+\n| JSON_EXTRACT(null, '$.type') | JSON_EXTRACT('{\"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_EXTRACT();<\/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_EXTRACT'<\/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_EXTRACT('{ \"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_EXTRACT'<\/pre>\n","protected":false},"excerpt":{"rendered":"
In MariaDB, JSON_EXTRACT() is a built-in function that extracts data from a JSON document, based on a given path or paths. It can return single values and multiple values. If a single value is matched, a single value is returned. If multiple values are matched, then those values are returned in an array.<\/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-18342","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\/18342","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=18342"}],"version-history":[{"count":7,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18342\/revisions"}],"predecessor-version":[{"id":18355,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18342\/revisions\/18355"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18342"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18342"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18342"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}