mysql> CREATE TABLE joson( id INT AUTO_INCREMENT PRIMARY KEY, context JSON NOT NULL)//
Query OK, 0 rows affected (0.05 sec)
mysql> DESC joson;
+---------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| context | json | NO | | NULL | |
+---------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT JSON_ARRAY(1,2,3,4,"AA","BB");
+-------------------------------+
| JSON_ARRAY(1,2,3,4,"AA","BB") |
+-------------------------------+
| [1, 2, 3, 4, "AA", "BB"] |
+-------------------------------+
1 row in set (0.13 sec)
mysql> SELECT JSON_OBJECT(1,2,3,4,"AA","BB")
-> ;
+--------------------------------+
| JSON_OBJECT(1,2,3,4,"AA","BB") |
+--------------------------------+
| {"1": 2, "3": 4, "AA": "BB"} |
+--------------------------------+
1 row in set (0.00 sec)
mysql> SET @data = JSON_OBJECT(1,2,3,4,"AA","BB");
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_PRETTY(@data);
+--------------------------------------+
| JSON_PRETTY(@data) |
+--------------------------------------+
| {
"1": 2,
"3": 4,
"AA": "BB"
} |
+--------------------------------------+
1 row in set (0.08 sec)
mysql> DESC joson;
+---------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| context | json | NO | | NULL | |
+---------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO joson(context) VALUES(@data);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM joson;
+----+------------------------------+
| id | context |
+----+------------------------------+
| 1 | {"1": 2, "3": 4, "AA": "BB"} |
+----+------------------------------+
1 row in set (0.00 sec)
mysql> SET @a = JSON_ARRAY(1,2,3,4);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+--------------+
| @a |
+--------------+
| [1, 2, 3, 4] |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY_INSERT(@a,"$[0]","quan");
+-------------------------------------+
| JSON_ARRAY_INSERT(@a,"$[0]","quan") |
+-------------------------------------+
| ["quan", 1, 2, 3, 4] |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY_INSERT(@a,"$[3]","zhi");
+------------------------------------+
| JSON_ARRAY_INSERT(@a,"$[3]","zhi") |
+------------------------------------+
| [1, 2, 3, "zhi", 4] |
+------------------------------------+
1 row in set (0.00 sec)
mysql> SET @a = JSON_ARRAY_INSERT(@a,"$[0]","quan");
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+----------------------+
| @a |
+----------------------+
| ["quan", 1, 2, 3, 4] |
+----------------------+
1 row in set (0.00 sec)
mysql> SET @a = JSON_ARRAY_INSERT(@a,"$[3]","zhi");
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+-----------------------------+
| @a |
+-----------------------------+
| ["quan", 1, 2, "zhi", 3, 4] |
+-----------------------------+
1 row in set (0.00 sec)
mysql> SET @b = JSON_OBJECT("id",11,"name","quanzhiqiang");
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @b;
+------------------------------------+
| @b |
+------------------------------------+
| {"id": 11, "name": "quanzhiqiang"} |
+------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_INSERT(@b,"$.age",90);
+-----------------------------------------------+
| JSON_INSERT(@b,"$.age",90) |
+-----------------------------------------------+
| {"id": 11, "age": 90, "name": "quanzhiqiang"} |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT @a;
+-----------------------------+
| @a |
+-----------------------------+
| ["quan", 1, 2, "zhi", 3, 4] |
+-----------------------------+
1 row in set (0.00 sec)
mysql> SELECT @b;
+------------------------------------+
| @b |
+------------------------------------+
| {"id": 11, "name": "quanzhiqiang"} |
+------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY_APPEND(@a,"$[0]","111","$[4]","4444");
+--------------------------------------------------+
| JSON_ARRAY_APPEND(@a,"$[0]","111","$[4]","4444") |
+--------------------------------------------------+
| [["quan", "111"], 1, 2, "zhi", [3, "4444"], 4] |
+--------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY_APPEND(@b,"$.name","namename");
+--------------------------------------------------+
| JSON_ARRAY_APPEND(@b,"$.name","namename") |
+--------------------------------------------------+
| {"id": 11, "name": ["quanzhiqiang", "namename"]} |
+--------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT @b;
+------------------------------------+
| @b |
+------------------------------------+
| {"id": 11, "name": "quanzhiqiang"} |
+------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_SET(@b,"$.id",90,"$.age",99);
+-----------------------------------------------+
| JSON_SET(@b,"$.id",90,"$.age",99) |
+-----------------------------------------------+
| {"id": 90, "age": 99, "name": "quanzhiqiang"} |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT @b;
+------------------------------------+
| @b |
+------------------------------------+
| {"id": 11, "name": "quanzhiqiang"} |
+------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_REPLACE(@b,"$.id",90,"$.age",99);
+---------------------------------------+
| JSON_REPLACE(@b,"$.id",90,"$.age",99) |
+---------------------------------------+
| {"id": 90, "name": "quanzhiqiang"} |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM joson;
+----+------------------------------+
| id | context |
+----+------------------------------+
| 1 | {"1": 2, "3": 4, "AA": "BB"} |
+----+------------------------------+
1 row in set (0.00 sec)
mysql> SELECT context FROM joson
-> ;
+------------------------------+
| context |
+------------------------------+
| {"1": 2, "3": 4, "AA": "BB"} |
+------------------------------+
1 row in set (0.00 sec)
mysql> UPDATE joson SET context = JSON_SET(context,"$.sex",1,"$.homw",2);
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM joson;
+----+---------------------------------------------------+
| id | context |
+----+---------------------------------------------------+
| 1 | {"1": 2, "3": 4, "AA": "BB", "sex": 1, "homw": 2} |
+----+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT @c;
+-----------------------+
| @c |
+-----------------------+
| {"sex": 1, "homw": 2} |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_REMOVE(@c,"$.sex");
+-------------------------+
| JSON_REMOVE(@c,"$.sex") |
+-------------------------+
| {"homw": 2} |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM joson;
+----+---------------------------------------------------+
| id | context |
+----+---------------------------------------------------+
| 1 | {"1": 2, "3": 4, "AA": "BB", "sex": 1, "homw": 2} |
+----+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> UPDATE joson SET context = JSON_REMOVE(context,"$.sex");
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM joson;
+----+-----------------------------------------+
| id | context |
+----+-----------------------------------------+
| 1 | {"1": 2, "3": 4, "AA": "BB", "homw": 2} |
+----+-----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_EXTRACT(context,"$.homw") FROM joson;
+--------------------------------+
| JSON_EXTRACT(context,"$.homw") |
+--------------------------------+
| 2 |
+--------------------------------+
1 row in set (0.00 sec)
mysql> SELECT context->"$.homw" FROM joson;
+-------------------+
| context->"$.homw" |
+-------------------+
| 2 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT @a;
+-----------------------------+
| @a |
+-----------------------------+
| ["quan", 1, 2, "zhi", 3, 4] |
+-----------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_LENGTH(@a);
+-----------------+
| JSON_LENGTH(@a) |
+-----------------+
| 6 |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT @b;
+------------------------------------+
| @b |
+------------------------------------+
| {"id": 11, "name": "quanzhiqiang"} |
+------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_LENGTH(@b);
+-----------------+
| JSON_LENGTH(@b) |
+-----------------+
| 2 |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS_PATH(@b,"one","$.id");
+-------------------------------------+
| JSON_CONTAINS_PATH(@b,"one","$.id") |
+-------------------------------------+
| 1 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS_PATH(@b,"one","$.idd");
+--------------------------------------+
| JSON_CONTAINS_PATH(@b,"one","$.idd") |
+--------------------------------------+
| 0 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS_PATH(@b,"one","$.id","$[11]");
+---------------------------------------------+
| JSON_CONTAINS_PATH(@b,"one","$.id","$[11]") |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT @a;
+-----------------------------+
| @a |
+-----------------------------+
| ["quan", 1, 2, "zhi", 3, 4] |
+-----------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS(@a,"1","$[1]");
+------------------------------+
| JSON_CONTAINS(@a,"1","$[1]") |
+------------------------------+
| 1 |
+------------------------------+
1 row in set (0.00 sec)
mysql> SELECT @a;
+-----------------------------+
| @a |
+-----------------------------+
| ["quan", 1, 2, "zhi", 3, 4] |
+-----------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS(@a,"1");
+-----------------------+
| JSON_CONTAINS(@a,"1") |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS(@a,"3");
+-----------------------+
| JSON_CONTAINS(@a,"3") |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)
