MySQL 支持JSON字段的基本操作、相关函数及索引使用如何索引JSON字段

CREATE TABLE `t` (  
    `id` INT UNSIGNED NOT NULL,
    `js` JSON NOT NULL,
    PRIMARY KEY (`id`)
);

{
    "num": 1,  
    "name": "abc",  
    "age": 16,  
    "newNum": 123,  
    "class":{    
       "one": {
          "num": 1
        },                                                                                                                          
       "tow": {
          "num": 2
        },  
       "three": {
          "num": 3
        }
     }
 }

insert into t values(1,'{"num":1,"name":"abc"}')

update t set js=json_set('{"num":1,"name":"abc"}','$.num',2,'$.age',16,'$.class.id',1) where id=1

结果js={"num":2,"name":"abc","age":16}

select json_extract('{"num":1,"name":"abc"}','$.num')，结果1

等价于
select js->'$.num' from t where id=1，结果1

select id,js->'$.name' 
from t 
where js->'$.age'=16 
order by js->'$.num'

等价于
select id,json_extract(js,'$.name') 
from t 
where json_extract(js,'$.age')=16
order by json_extract(js,'$.num')

select * from t where JSON_CONTAINS(js,JSON_OBJECT('num', 1))

select t.js,t.js->"$.name" from table t 
where js_name(t.js->"$.name","\"b\"")

SELECT JSON_SEARCH('["1","2","3","4","5"]', 'one', "2") is not null; 结果返回1

SELECT JSON_SEARCH('["1","2","3","4","5"]', 'one', "6") is not null; 结果返回0

-- 要查找的值
SET @valueMapping = 17;

-- 表字段：id, config
-- config字段格式：
/*
{
    "fieldModels": [{
        "key": 0,
        "guid": "1",
        "field": "Id",
        "dataType": 1,
        "showName": "标识",
        "textFormat": "",
        "valueMapping": 17
    }, {
        "key": 1,
        "guid": "2",
        "field": "orderid",
        "dataType": 0,
        "showName": "orderid",
        "textFormat": "",
        "valueMapping": -1
    }
}
*/
-- 需求：查找 config JSON字段（对象类型）中 fieldModels（数组类型）数组字段中 valueMapping（整形）值等于 17 的记录

-- 1、先提取 config JSON 字段中 fieldModels 属性，得到数组
SELECT JSON_EXTRACT(`config`,'$.fieldModels') fieldModels FROM `sql_model`;

-- 2、再从 fieldModels 数组中查找 valueMapping 的值是否等于查找的值，返回 1 或 0，表示是否已找到
SELECT JSON_CONTAINS(JSON_EXTRACT(`config`,'$.fieldModels'), JSON_OBJECT('valueMapping', @valueMapping)) 是否已找到 FROM `sql_model`;

-- 最后匹配结果
SELECT id, config FROM `sql_model` 
WHERE JSON_CONTAINS(JSON_EXTRACT(`config`,'$.fieldModels'), JSON_OBJECT('valueMapping', @valueMapping)) > 0;

SET @j = '{"0": {"a":1, "b":2}, "other": {"c": 3, "d": 4}}';
SELECT JSON_EXTRACT(@j, '$.0.a') AS '$.0.a'

SELECT JSON_EXTRACT(@j, '$."0".a') AS `$.0.a`;

select * from t where js->'$.num'=1

update t set js=json_array_append(js,'$.newNum',123) where id =1

<type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ]
[ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]

CREATE TABLE `t` (  
    `id` INT UNSIGNED NOT NULL,
    `js` JSON NOT NULL,
    js_num int(11) GENERATED ALWAYS AS (json_extract(js,'$.num')) STORED,
--   `js_num` int(11) GENERATED ALWAYS AS (`js` ->> '$.num'), 
    PRIMARY KEY (id)
);

SHOW COLUMNS FROM `t`;

+------------------+------------------+------+-----+---------+-------------------+
| Field            | Type             | Null | Key | Default | Extra             |
+------------------+------------------+------+-----+---------+-------------------+
| id               | int(10) unsigned | NO   | PRI | NULL    |                   |
| js               | json             | NO   |     | NULL    |                   |
| js_num           | int(11)          |      |     | NULL    | VIRTUAL GENERATED |
+------------------+------------------+------+-----+---------+-------------------+

EXPLAIN SELECT * FROM `t` WHERE `js_num` = 1  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ALL
possible_keys: NULL  
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where

CREATE INDEX `index_js_num` ON `t`(`js_num`);  

EXPLAIN SELECT * FROM `t` WHERE `js_num` = 1  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ref
possible_keys: names_idx  
          key: names_idx
      key_len: 22
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL