{"id":18486,"date":"2021-07-13T08:03:15","date_gmt":"2021-07-12T22:03:15","guid":{"rendered":"https:\/\/database.guide\/?p=18486"},"modified":"2021-07-13T08:03:15","modified_gmt":"2021-07-12T22:03:15","slug":"mariadb-json_type-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/mariadb-json_type-explained\/","title":{"rendered":"MariaDB JSON_TYPE() Explained"},"content":{"rendered":"\n

In MariaDB, JSON_TYPE()<\/code> is a built-in function that returns the type of a JSON value, as a string. <\/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_TYPE(json_val)<\/code><\/pre>\n\n\n\n
Where json_val<\/code> is the value for which to return the type.<\/p>\n\n\n\n
Object Types<\/h2>\n\n\n\n
The possible return types are listed in the following table:<\/p>\n\n\n\n
Return type<\/th>Value<\/th>Example<\/th><\/tr>
ARRAY<\/code><\/td>JSON array.<\/td>[1, 2, 3]<\/code><\/td><\/tr>
OBJECT<\/code><\/td>JSON object.<\/td>{\"a\":\"1\"}<\/code><\/td><\/tr>
BOOLEAN<\/code><\/td>JSON true\/false literal.<\/td>true<\/code> or false<\/code><\/td><\/tr>
DOUBLE<\/code><\/td>A number with at least one floating point decimal.<\/td>1.2<\/code><\/td><\/tr>
INTEGER<\/code><\/td>A number without a floating point decimal.<\/td>1<\/code><\/td><\/tr>
NULL<\/code><\/td>JSON null<\/code> literal. This is returned as a string, and is not to be confused with the SQL NULL<\/code> value.<\/td>null<\/code><\/td><\/tr>
STRING<\/code><\/td>JSON string.<\/td>\"bird\"<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s an example to demonstrate.<\/p>\n\n\n\n
SET @json = '\n    { \n        \"name\" : \"Fluffy\", \n        \"type\" : \"Cat\" \n    }\n';\n\nSELECT JSON_TYPE(@json);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------+\n| JSON_TYPE(@json) |\n+------------------+\n| OBJECT           |\n+------------------+<\/pre>\n\n\n\n
In this example I passed a whole JSON document, which has a type of OBJECT<\/code>.<\/p>\n\n\n\n
Here are more examples:<\/p>\n\n\n\n
SELECT \n    JSON_TYPE('[1, 2, 3]'),\n    JSON_TYPE('{ \"a\" : 1 }'),\n    JSON_TYPE('true'),\n    JSON_TYPE('false'),\n    JSON_TYPE(10.59),\n    JSON_TYPE(10),\n    JSON_TYPE(null),\n    JSON_TYPE('\"Fuzzy Smith\"');<\/code><\/pre>\n\n\n\n
Result (using vertical output):<\/p>\n\n\n\n
    JSON_TYPE('[1, 2, 3]'): ARRAY\n  JSON_TYPE('{ \"a\" : 1 }'): OBJECT\n         JSON_TYPE('true'): BOOLEAN\n        JSON_TYPE('false'): BOOLEAN\n          JSON_TYPE(10.59): DOUBLE\n             JSON_TYPE(10): INTEGER\n           JSON_TYPE(null): NULL\nJSON_TYPE('\"Fuzzy Smith\"'): STRING<\/pre>\n\n\n\n
Extract the Value from a JSON Document<\/h2>\n\n\n\n
In the previous examples, I passed each value directly to the function as a literal. <\/p>\n\n\n\n
We can combine JSON_TYPE()<\/code> with other functions, such as JSON_EXTRACT()<\/a><\/code> to find out the type of a value within a larger JSON document. Here’s an example of extracting a value from a JSON document in order to find out its type:<\/p>\n\n\n\n
SET @json = '\n    { \n        \"name\" : \"Wag\", \n        \"scores\" : [8, 0, 9] \n    }\n';\n\nSELECT \n    JSON_TYPE(JSON_EXTRACT(@json, '$.name')) AS Result;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------+\n| Result |\n+--------+\n| STRING |\n+--------+<\/pre>\n\n\n\n
Here’s another example that returns more types:<\/p>\n\n\n\n
SET @json = '\n    { \n        \"name\" : \"Wag\", \n        \"scores\" : [8, 0, 9],\n        \"weight\" : 10.50,\n        \"height\" : null,\n        \"age\" : 4\n    }\n';\n\nSELECT \n    JSON_TYPE(\n        JSON_EXTRACT(@json, '$.name')\n        ) AS a,\n    JSON_TYPE(\n        JSON_EXTRACT(@json, '$.scores')\n        ) AS b,\n    JSON_TYPE(\n        JSON_EXTRACT(@json, '$.scores[0]')\n        ) AS c,\n    JSON_TYPE(\n        JSON_EXTRACT(@json, '$.weight')\n        ) AS d,\n    JSON_TYPE(\n        JSON_EXTRACT(@json, '$.height')\n        ) AS e,\n    JSON_TYPE(\n        JSON_EXTRACT(@json, '$.age')\n        ) AS f;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------+-------+---------+--------+------+---------+\n| a      | b     | c       | d      | e    | f       |\n+--------+-------+---------+--------+------+---------+\n| STRING | ARRAY | INTEGER | DOUBLE | NULL | INTEGER |\n+--------+-------+---------+--------+------+---------+<\/pre>\n\n\n\n
Null Argument<\/h2>\n\n\n\n
If the argument is null<\/code>, then null<\/code> is returned.<\/p>\n\n\n\n
SELECT JSON_TYPE(null);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------------+\n| JSON_TYPE(null) |\n+-----------------+\n| NULL            |\n+-----------------+<\/pre>\n\n\n\n
Incorrect Parameter Count<\/h2>\n\n\n\n
Calling JSON_TYPE()<\/code> without an argument results in an error:<\/p>\n\n\n\n
SELECT JSON_TYPE();<\/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_TYPE'<\/pre>\n\n\n\n
It’s the same when too many arguments are passed:<\/p>\n\n\n\n
SELECT JSON_TYPE(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_TYPE'<\/pre>\n","protected":false},"excerpt":{"rendered":"
In MariaDB, JSON_TYPE() is a built-in function that returns the type of a JSON value, as a string. <\/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-18486","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\/18486","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=18486"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18486\/revisions"}],"predecessor-version":[{"id":18677,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18486\/revisions\/18677"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18486"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18486"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18486"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}