Basic Query On JSON Datatype | Mysql

mysql_json.log

mysql> use test;
mysql> create table sample_json(id INT, data JSON);
Query OK, 0 rows affected (0.03 sec)

mysql> describe sample_json;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| data  | json    | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into sample_json value(0, '{"name": "Digvijay", "age":27, "single": true}');
Query OK, 1 row affected (0.00 sec)

mysql> select * from sample_json;
+------+-------------------------------------------------+
| id   | data                                            |
+------+-------------------------------------------------+
|    0 | {"age": 27, "name": "Digvijay", "single": true} |
+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_type('"diggu"');
+----------------------+
| json_type('"diggu"') |
+----------------------+
| STRING               |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2018-01-01 16:52:36.000000"] |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select  data->"$.name" from sample_json;
+----------------+
| data->"$.name" |
+----------------+
| "Digvijay"     |
+----------------+
1 row in set (0.00 sec)

mysql> select  data->"$.age" from sample_json;
+---------------+
| data->"$.age" |
+---------------+
| 27            |
+---------------+
1 row in set (0.00 sec)

mysql> select  data->"$.age" as AGE from sample_json;
+------+
| AGE  |
+------+
| 27   |
+------+
1 row in set (0.00 sec)

mysql> insert into sample_json value(0, '{"name": "Digvijay", "age":27, "single": true, "address": {"Street": "Park St.", "Area": "North Bay", "City": "Hyderbad", "state":"TS" }}');
Query OK, 1 row affected (0.01 sec)

mysql> select  data->"$.age" as AGE from sample_json;                                      +------+
| AGE  |
+------+
| 27   |
| 27   |
+------+
2 rows in set (0.00 sec)

mysql> select  data->"$.name" from sample_json;                                      
+----------------+
| data->"$.name" |
+----------------+
| "Digvijay"     |
| "Digvijay"     |
+----------------+
2 rows in set (0.00 sec)

mysql> select  data->"$.address" as AGE from sample_json;
+--------------------------------------------------------------------------------+
| AGE                                                                            |
+--------------------------------------------------------------------------------+
| NULL                                                                           |
| {"Area": "North Bay", "City": "Hyderbad", "state": "TS", "Street": "Park St."} |
+--------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select  data->"$.address.City" as AGE from sample_json;
+------------+
| AGE        |
+------------+
| NULL       |
| "Hyderbad" |
+------------+
2 rows in set (0.00 sec)

mysql> select  data as INFO from sample_json Where data->"$.address.state" = "TS";
+--------------------------------------------------------------------------------------------------------------------------------------------+
| INFO                                                                                                                                       |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| {"age": 27, "name": "Digvijay", "single": true, "address": {"Area": "North Bay", "City": "Hyderbad", "state": "TS", "Street": "Park St."}} |
+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select  data->"$.address.City" as INFO from sample_json Where data->"$.address.state" = "TS";
+------------+
| INFO       |
+------------+
| "Hyderbad" |
+------------+
1 row in set (0.00 sec)

mysql> select  data->"$.address.City" as INFO from sample_json Where data->"$.address.state" = null;
Empty set (0.01 sec)

mysql> select  data->"$.address.City" as INFO from sample_json Where data->"$.address" = null;
Empty set (0.00 sec)

mysql> select  data->"$.address.City" as INFO from sample_json Where data->"$.age" = 27;
+------------+
| INFO       |
+------------+
| NULL       |
| "Hyderbad" |
+------------+
2 rows in set (0.00 sec)

mysql> select  data->"$.address.Street" as INFO from sample_json Where data->"$.age" = 27;
+------------+
| INFO       |
+------------+
| NULL       |
| "Park St." |
+------------+
2 rows in set (0.00 sec)

mysql> select  data->"$.address.NotAField" as INFO from sample_json Where data->"$.age" = 27;
+------+
| INFO |
+------+
| NULL |
| NULL |
+------+
2 rows in set (0.00 sec)

mysql> select count(data->"$.address.NotAField") as INFO from sample_json Where data->"$.age" = 27;
+------+
| INFO |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> select data from sample_json;
+--------------------------------------------------------------------------------------------------------------------------------------------+
| data                                                                                                                                       |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| {"age": 27, "name": "Digvijay", "single": true}                                                                                            |
| {"age": 27, "name": "Digvijay", "single": true, "address": {"Area": "North Bay", "City": "Hyderbad", "state": "TS", "Street": "Park St."}} |
+--------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.03 sec)