{"id":18367,"date":"2021-07-09T09:11:46","date_gmt":"2021-07-08T23:11:46","guid":{"rendered":"https:\/\/database.guide\/?p=18367"},"modified":"2021-07-09T09:11:46","modified_gmt":"2021-07-08T23:11:46","slug":"mariadb-json_keys-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/mariadb-json_keys-explained\/","title":{"rendered":"MariaDB JSON_KEYS() Explained"},"content":{"rendered":"\n

In MariaDB, JSON_KEYS()<\/code> is a built-in function that returns an array of the top-level keys from a JSON document. If a path is provided, it returns the top-level keys from that path.<\/p>\n\n\n\n

It excludes keys from nested sub-objects in the specified level. <\/p>\n\n\n\n

Also, if the selected object is empty, an empty array is returned.<\/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_KEYS(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_KEYS(@json_document);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+----------------------------+\n| JSON_KEYS(@json_document)  |\n+----------------------------+\n| [\"name\", \"type\", \"weight\"] |\n+----------------------------+<\/pre>\n\n\n\n
Specifying a Path<\/h2>\n\n\n\n
Here’s an example of specifying a path within the document:<\/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_KEYS(\n    @json_document, \n    '$.details'\n    ) AS Result;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------------------+\n| Result                       |\n+------------------------------+\n| [\"type\", \"weight\", \"awards\"] |\n+------------------------------+<\/pre>\n\n\n\n
As mentioned, sub-objects are excluded from the result.<\/p>\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        \"_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_KEYS(\n    @json_document, \n    '$.wrong'\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
Empty Objects<\/h2>\n\n\n\n
If the selected object is empty, an empty array is returned:<\/p>\n\n\n\n
SELECT JSON_KEYS('{}');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------------+\n| JSON_KEYS('{}') |\n+-----------------+\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_KEYS(null),\n    JSON_KEYS(null, '$.type'),\n    JSON_KEYS('{\"a\":1}', null);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------------+---------------------------+----------------------------+\n| JSON_KEYS(null) | JSON_KEYS(null, '$.type') | JSON_KEYS('{\"a\":1}', null) |\n+-----------------+---------------------------+----------------------------+\n| NULL            | 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_KEYS();<\/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_KEYS'<\/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_KEYS('{ \"a\": 1}', 1, 2);<\/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_KEYS'<\/pre>\n","protected":false},"excerpt":{"rendered":"
In MariaDB, JSON_KEYS() is a built-in function that returns an array of the top-level keys from a JSON document. If a path is provided, it returns the top-level keys from that path. It excludes keys from nested sub-objects in the specified level. Also, if the selected object is empty, an empty array is returned.<\/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-18367","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\/18367","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=18367"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18367\/revisions"}],"predecessor-version":[{"id":18373,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18367\/revisions\/18373"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18367"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18367"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18367"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}