分享宗旨：能不做绝不做，能抄绝不自己写，能少写绝不多写。

image.png

image.png

image.png

image.png

image.png

image.png

image.png

image.png

image.png

image.png

json函数简单示例

CREATE TABLE tt (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
a json DEFAULT NULL,
b varchar(255) DEFAULT NULL,
c varchar(255) DEFAULT NULL,
d varchar(255) DEFAULT NULL,
e varchar(255) DEFAULT NULL,
create_time datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO tt VALUES (1, '{"a": 1, "aa": "11"}', '1', '1', '1', '1', '2023-09-07 13:26:08');
INSERT INTO tt VALUES (2, '{"a": 1, "b": "11", "aa": "2"}', '2', '2', '2', '2', '2023-09-07 13:33:24');
INSERT INTO tt VALUES (3, '{"a": 1, "aa": "11"}', '3', '3', '3', '3', '2023-09-07 13:28:02');
INSERT INTO tt VALUES (4, '{"a": 1, "aa": "11"}', '4', '4', '4', '4', '2023-09-07 13:28:02');
INSERT INTO tt VALUES (5, '{"a": 1, "aa": "11"}', '5', '5', '5', '5', '2023-09-07 13:28:02');
INSERT INTO tt VALUES (6, '{"a": 1, "aa": "11"}', '6', '6', '6', '6', '2023-09-07 13:28:02');

image.png

-- 数量

SELECT
    COUNT(
    JSON_EXTRACT( a, '$.a' )) count_a,
    COUNT(
    JSON_EXTRACT( a, '$.aa' )),
    COUNT(
    JSON_EXTRACT( a, '$.b' )) count_aaa 
FROM
    tt;

    
-- 一般函数使用
SELECT SUM(JSON_EXTRACT(a, '$.aa')) FROM tt;    


-- json类型字段 根据key分组 

SELECT a->'$.aa', COUNT(*) FROM tt GROUP BY a->'$.aa';