MYSQL8.0-JSON函数

官网地址：https://dev.mysql.com/doc/refman/8.0/en/json-functions.html

• 创建对象

SELECT t.code,JSON_OBJECT(t.code,t.name,t.sub_code,t.sub_value) FROM cms_config AS t
参数列必须为偶数，为奇数则报错

• 搜索属性

SELECT t.sub_value, JSON_CONTAINS_PATH(t.sub_value,'one','$.k1','&.k2') FROM cms_config AS t WHERE CODE='TEMPLATE_TYPE'
 'one': 如果文档中至少存在一个路径，则为 1，否则为 0。
 'all': 如果所有路径都存在于文档中，则为 1，否则为 0。

SELECT t.code, JSON_CONTAINS(t.sub_value,'"v1"','$.k1') FROM cms_config AS t WHERE CODE='TEMPLATE_TYPE'
参数2中也必须是符合参数1的json格式

JSON类型字段，生成JSON列索引

mysql> CREATE TABLE jemp (
    ->     c JSON,
    ->     g INT GENERATED ALWAYS AS (c->"$.id"),
    ->     INDEX i (g)
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO jemp (c) VALUES
     >   ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
     >   ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT c->>"$.name" AS name
     >     FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT c->>"$.name" AS name
     >    FROM jemp WHERE g > 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name'))
AS `name` from `test`.`jemp` where (`test`.`jemp`.`g` > 2)
1 row in set (0.00 sec)