SQL的JSON操作

CREATE TABLE `users` (
  `id` int(11) AUTO_INCREMENT NOT NULL,
  `username` varchar(50)  NOT NULL,
  `sex` varchar(50)  NOT NULL,
  `email` varchar(100)  NOT NULL,
  `password` varchar(100)  NOT NULL,
  `feature` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `test`.`users`(`id`, `username`, `sex`, `email`, `password`, `feature`) VALUES (1, 'john.doe', '男', 'john@example.com', 'password123', '{\"env\": \"pre\", \"obj\": {\"name\": \"lzh\"}, \"tags\": [\"A\", \"B\"]}');
INSERT INTO `test`.`users`(`id`, `username`, `sex`, `email`, `password`, `feature`) VALUES (2, 'jane.smith', '男', 'jane@example.com', 'password456', NULL);
INSERT INTO `test`.`users`(`id`, `username`, `sex`, `email`, `password`, `feature`) VALUES (3, 'mark.wilson', '女', 'mark@example.com', 'password789', NULL);

查询json字段

SELECT feature->'$.env' FROM users;
SELECT feature->'$.obj.name' FROM users;

JSON取值

select feature ->> '$.venture',
       feature -> '$.venture',
       JSON_UNQUOTE(feature -> '$.venture') as venture,
       JSON_UNQUOTE(json_extract(feature, '$.venture')),
       json_extract(feature, '$.venture')
from users
where id = 3

判断数组字段是否包含某个值

SELECT * FROM users WHERE JSON_CONTAINS(feature->'$.tags', '"A"');

查询数组字段的长度

SELECT JSON_LENGTH(feature->'$.tags') FROM users;

where 条件查询

SELECT * FROM users WHERE feature->'$.env' = 'pre'

json对象的key

SELECT JSON_KEYS(feature) FROM users;

结果：["dev", "obj", "tags"]

更新JSON字段

-- 更新JSON字段
UPDATE users SET feature = JSON_SET(feature, '$.env', 'online') WHERE id = 1;
-- 更新多个字段
update users set feature = json_set(feature, '$.name', '喵喵喵', '$.age', 120) where id = 1;

删除JSON字段

UPDATE users SET data = JSON_REMOVE(feature, '$.env') WHERE id = 1;

JSON_TABLE语法

sku_list 是一个json array 数组，将array里面的object给提取出来

SELECT
  xx.id,
  min(unit_cnt_3m) as 'sku_min_unit_cnt_3m',
  max(unit_cnt_3m) as 'sku_max_unit_cnt_3m',
  min(unit_cnt_1d) as 'sku_min_unit_cnt_1d',
  max(unit_cnt_1d) as 'sku_max_unit_cnt_1d'
FROM
  lzd_choice_qualify_product as xx,
  JSON_TABLE(
    sku_list,
    '$[*]' COLUMNS (
      unit_cnt_3m VARCHAR(100) PATH '$.unit_cnt_3m',
      unit_cnt_1d VARCHAR(100) PATH '$.unit_cnt_1d'
    )
  ) AS jt
where
  ds = '20241126'
GROUP BY
  id