JSON_TYPE()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Topics on this page:
Overview
Returns the type of an item of JSON data.
USAGE
JSON_TYPE(json_data)
Argument Name | Description |
|---|---|
| The JSON data to check |
DETAILS
JSON_TYPE() is a JSON informational function that inspects valid JSON data and returns its data type.
JSON data is valid if it conforms to ECMA-404: The JSON Data Interchange Standard.
The type returned is one of the following values:
ARRAY- a JSON arrayOBJECT- a JSON objectSTRING- a JSON stringINTEGER- an integer value (non-quoted)DECIMAL- a floating point value (non-quoted)BOOLEAN- a JSON boolean value oftrueorfalseNULL- a JSON null value
Note that MariaDB Server returns DOUBLE instead of DECIMAL.
EXAMPLES
With Literal Values
In the following example, JSON_TYPE() is called with literal values:
SELECT JSON_TYPE('{"bar": "a string"}') AS type_1,
JSON_TYPE('[1,2]') AS type_2,
JSON_TYPE(1) AS type_3,
JSON_TYPE(1.5) AS type_4,
JSON_TYPE('"a"') AS type_5,
JSON_TYPE('false') AS type_6,
JSON_TYPE('null') AS type_7\G
*************************** 1. row ***************************
type_1: OBJECT
type_2: ARRAY
type_3: INTEGER
type_4: DECIMAL
type_5: STRING
type_6: BOOLEAN
type_7: NULL
SELECT JSON_TYPE(NULL);
+-----------------+
| JSON_TYPE(NULL) |
+-----------------+
| NULL |
+-----------------+
Example Schema and Data
Some of the examples are based on the type_test table:
CREATE TABLE type_test (
example JSON
);
INSERT INTO type_test VALUES
('{"bar": "hi", "foo": 42}'),
('[1, 2, 3]'),
('"Now is the time"');
Per-row Values
SELECT example, JSON_TYPE(example) AS 'type'
FROM type_test\G
*************************** 1. row ***************************
example: {"bar": "hi", "foo": 42}
type: OBJECT
*************************** 2. row ***************************
example: [1, 2, 3]
type: ARRAY
*************************** 3. row ***************************
example: "Now is the time"
type: STRING
JSON Type at Specific Path
SELECT JSON_TYPE(JSON_EXTRACT(example, '$.bar')) as bar_type
FROM type_test;
+----------+
| bar_type |
+----------+
| STRING |
| NULL |
| NULL |
+----------+