json表操作 & 索引

MySQL [test]> create table t2(a json,b INT);
MySQL [test]> show create table t2;
| t2    | CREATE TABLE `t2` (
  `a` json DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

MySQL [test]> insert into t2 values("[2,32,41]",33),("[3,13,2,[22,44,55]]",0);

MySQL [test]> select * from t2;
+--------------------------+------+
| a                        | b    |
+--------------------------+------+
| [2, 32, 41]              |   33 |
| [3, 13, 2, [22, 44, 55]] |    0 |
+--------------------------+------+

MySQL [test]> select a->"$[0]" from t2;
+-----------+
| a->"$[0]" |
+-----------+
| 2         |
| 3         |
+-----------+
2 rows in set (0.00 sec)

MySQL [test]> select json_extract(`a`,"$[0]") from t2;
+--------------------------+
| json_extract(`a`,"$[0]") |
+--------------------------+
| 2                        |
| 3                        |
+--------------------------+

MySQL [test]> select a->"$[3]" from t2; 
+--------------+
| a->"$[3]"    |
+--------------+
| NULL         |
| [22, 44, 55] |
+--------------+

MySQL [test]> select `a` from t2 where json_extract(`a`,"$[0]")>2;
+--------------------------+
| a                        |
+--------------------------+
| [3, 13, 2, [22, 44, 55]] |
+--------------------------+

MySQL [test]> insert into t2 values('"aa"',10);

MySQL [test]> select * from t2;
+--------------------------+------+
| a                        | b    |
+--------------------------+------+
| [2, 32, 41]              |   33 |
| [3, 13, 2, [22, 44, 55]] |    0 |
| "aa"                     |   10 |
+--------------------------+------+

MySQL [test]> delete from t2 where `a`->"$[0]" = "aa";
Query OK, 1 row affected (0.00 sec)

MySQL [test]> select * from t2;
+--------------------------+------+
| a                        | b    |
+--------------------------+------+
| [2, 32, 41]              |   33 |
| [3, 13, 2, [22, 44, 55]] |    0 |
+--------------------------+------+


1

• 使用索引

As noted elsewhere, JSON columns cannot be indexed directly. To create an index that references such a column indirectly, you can define a generated column that extracts the information that should be indexed, then create an index on the generated column, as shown in this example:

MySQL [test]> create table t3(c json, g int generated always as (json_extract(c,"$.id")),index i (g));
Query OK, 0 rows affected (0.01 sec)

insert into t3 (c) values ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

MySQL [test]> select * from t3;
+-------------------------------+------+
| c                             | g    |
+-------------------------------+------+
| {"id": "1", "name": "Fred"}   |    1 |
| {"id": "2", "name": "Wilma"}  |    2 |
| {"id": "3", "name": "Barney"} |    3 |
| {"id": "4", "name": "Betty"}  |    4 |
+-------------------------------+------+

MySQL [test]> select `c`->"$.id" from t3 where `g` > 2;
+-------------+
| `c`->"$.id" |
+-------------+
| "3"         |
| "4"         |
+-------------+
2 rows in set (0.00 sec)

MySQL [test]> select `c`->"$.name" from t3 where `g` > 2;  
+---------------+
| `c`->"$.name" |
+---------------+
| "Barney"      |
| "Betty"       |
+---------------+
2 rows in set (0.00 sec)

转载于:https://my.oschina.net/sunpy/blog/615726