1.JSON_DEPTH(json_doc)
返回JSON document的最大深度。空数组、空对象或标量值的深度为1。一个只包含深度为1的元素的非空数组, 或仅包含深度为1的元素的非空对象,,其深度为2。否则,JSON文档的深度大于2。
mysql> SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true'); +------------------+------------------+--------------------+ | JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH('true') | +------------------+------------------+--------------------+ | 1 | 1 | 1 | +------------------+------------------+--------------------+ mysql> SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]'); +------------------------+------------------------+ | JSON_DEPTH('[10, 20]') | JSON_DEPTH('[[], {}]') | +------------------------+------------------------+ | 2 | 2 | +------------------------+------------------------+ mysql> SELECT JSON_DEPTH('[10, {"a": 20}]'); +-------------------------------+ | JSON_DEPTH('[10, {"a": 20}]') | +-------------------------------+ | 3 | +-------------------------------+
2.JSON_LENGTH(json_doc[, path])
获取指定范围(path)内的 JSON document 长度。如果path参数不存在,则获取的是最外层的 JSON document。
mysql> SELECT JSON_LENGTH('[1, 2, {"a": 3}]'); +---------------------------------+ | JSON_LENGTH('[1, 2, {"a": 3}]') | +---------------------------------+ | 3 | +---------------------------------+ mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}'); +-----------------------------------------+ | JSON_LENGTH('{"a": 1, "b": {"c": 30}}') | +-----------------------------------------+ | 2 | +-----------------------------------------+ mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b'); +------------------------------------------------+ | JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') | +------------------------------------------------+ | 1 | +------------------------------------------------+
3.JSON_TYPE(json_val)
以一个utf8mb4字符串返回JSON类型
mysql> SET @j = '{"a": [10, true]}'; mysql> SELECT JSON_TYPE(@j); +---------------+ | JSON_TYPE(@j) | +---------------+ | OBJECT | +---------------+ mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a')); +------------------------------------+ | JSON_TYPE(JSON_EXTRACT(@j, '$.a')) | +------------------------------------+ | ARRAY | +------------------------------------+ mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]')); +---------------------------------------+ | JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]')) | +---------------------------------------+ | INTEGER | +---------------------------------------+ mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]')); +---------------------------------------+ | JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]')) | +---------------------------------------+ | BOOLEAN | +---------------------------------------+
mysql> SELECT JSON_TYPE(NULL); +-----------------+ | JSON_TYPE(NULL) | +-----------------+ | NULL | +-----------------+
JSON_TYPE()返回的类型有:
纯JSON类型:
OBJECT: JSON对象
ARRAY: JSON数组
BOOLEAN: JSON的true, false
NULL: JSON null
数字类型:
INTEGER: 对应MySQL的TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
DOUBLE: 对应MySQL的DOUBLE, FLOAT
DECIMAL: 对应MySQL的DECIMAL, NUMERIC
时间类型:
DATETIME: 对应MySQL的DATETIME, TIMESTAMP
ARRAY: 对应MySQL的DATE
BOOLEAN: 对应MySQL的TIME
字符串类型:
STRING: 对应MySQL的CHAR, VARCHAR, TEXT, ENUM, and SET
二进制类型:
BLOB: 对应MySQL的BINARY, VARBINARY, BLOB
BIT: 对应MySQL的BIT
其他类型:
OPAQUE(raw bits)
4.JSON_VALID(val)
判断给出的值是否是一个有效的JSON字符串。
mysql> SELECT JSON_VALID('{"a": 1}'); +------------------------+ | JSON_VALID('{"a": 1}') | +------------------------+ | 1 | +------------------------+ mysql> SELECT JSON_VALID('hello'), JSON_VALID('"hello"'); +---------------------+-----------------------+ | JSON_VALID('hello') | JSON_VALID('"hello"') | +---------------------+-----------------------+ | 0 | 1 | +---------------------+-----------------------+