创建json表:
CREATE TABLE t1 (jdoc JSON);
插入json数据:
INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
JSON_TYPE
mysql> SELECT JSON_TYPE('["a", "b", 1]');
+----------------------------+
| JSON_TYPE('["a", "b", 1]') |
+----------------------------+
| ARRAY |
+----------------------------+
mysql> SELECT JSON_TYPE('"hello"');
+----------------------+
| JSON_TYPE('"hello"') |
+----------------------+
| STRING |
+----------------------+
mysql> SELECT JSON_TYPE('hello');
ERROR 3146 (22032): Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.
JSON_ARRAY
mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW()) |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+
JSON_OBJECT
mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"} |
+---------------------------------------+
#使用最先的value
mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
+------------------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": 1, "key2": "abc"} |
+------------------------------------------------------+
JSON_MERGE (values 个数大于1)
MySQL [test]> select json_merge('"aa"','12','[1,2,"bb"]','{"key1": 1, "key2": "aaaa"}');
+--------------------------------------------------------------------+
| json_merge('"aa"','12','[1,2,"bb"]','{"key1": 1, "key2": "aaaa"}') |
+--------------------------------------------------------------------+
| ["aa", 12, 1, 2, "bb", {"key1": 1, "key2": "aaaa"}] |
+--------------------------------------------------------------------+
#多值合并
mysql> SELECT JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}');
+----------------------------------------------------+
| JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}') |
+----------------------------------------------------+
| {"a": [1, 4], "b": 2, "c": 3} |
+----------------------------------------------------+
定义json变量
mysql> SET @j = JSON_OBJECT('key', 'value');
mysql> SELECT @j;
+------------------+
| @j |
+------------------+
| {"key": "value"} |
+------------------+
关于null
mysql> SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL');
+--------------------+--------------------+--------------------+
| JSON_VALID('null') | JSON_VALID('Null') | JSON_VALID('NULL') |
+--------------------+--------------------+--------------------+
| 1 | 0 | 0 |
+--------------------+--------------------+--------------------+
mysql> SELECT ISNULL(null), ISNULL(Null), ISNULL(NULL);
+--------------+--------------+--------------+
| ISNULL(null) | ISNULL(Null) | ISNULL(NULL) |
+--------------+--------------+--------------+
| 1 | 1 | 1 |
+--------------+--------------+--------------+
JSON_EXTRACT
MySQL [test]> select json_extract('{"id":11,"name":"sun"}','$.name');
+-------------------------------------------------+
| json_extract('{"id":11,"name":"sun"}','$.name') |
+-------------------------------------------------+
| "sun" |
+-------------------------------------------------+
.[*] evaluates to the values of all members in a JSON object. 通配符用法: $.c[*]
[*] evaluates to the values of all elements in a JSON array.
$**.b evaluates to multiple paths ($.a.b and $.c.b)
[3, {"a": [5, 6], "b": 10}, [99, 100]]
Then:
$[0] evaluates to 3.
$[1] evaluates to {"a": [5, 6], "b": 10}.
$[2] evaluates to [99, 100].
$[3] evaluates to NULL (it refers to the fourth array element, which does not exist).
Because $[1] and $[2] evaluate to nonscalar values, they can be used as the basis for more-specific path expressions that select nested values. Examples:
$[1].a evaluates to [5, 6].
$[1].a[1] evaluates to 6.
$[1].b evaluates to 10.
$[2][0] evaluates to 99.
JSON_SET
mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';
mysql> SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+--------------------------------------------+
| JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+--------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20, 2]] |
+--------------------------------------------+
JSON_INSERT
mysql> SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+-----------------------------------------------+
| JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+-----------------------------------------------+
| ["a", {"b": [true, false]}, [10, 20, 2]] |
+-----------------------------------------------+
JSON_REPLACE
mysql> SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+------------------------------------------------+
| JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+------------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20]] |
+------------------------------------------------+
JSON_REMOVE
mysql> SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
+---------------------------------------------------+
| JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') |
+---------------------------------------------------+
| ["a", {"b": [true]}] |
+---------------------------------------------------+
JSON_CONTAINS
@j中是否包含@j2 的值
mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SET @j2 = '1';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
| 1 |
+-------------------------------+
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.b') |
+-------------------------------+
| 0 |
+-------------------------------+
mysql> SET @j2 = '{"d": 4}';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
| 0 |
+-------------------------------+
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.c') |
+-------------------------------+
| 1 |
+-------------------------------+
JSON_KEYS
mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
+---------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
+---------------------------------------+
| ["a", "b"] |
+---------------------------------------+
mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
+----------------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
+----------------------------------------------+
| ["c"] |
+----------------------------------------------+