mysql中jsonarray的查询和修改

数据初始化

/*
 Navicat Premium Data Transfer

 Source Server         : 47.100.14.70
 Source Server Type    : MySQL
 Source Server Version : 80022
 Source Host           : 47.100.14.70:3306
 Source Schema         : price

 Target Server Type    : MySQL
 Target Server Version : 80022
 File Encoding         : 65001

 Date: 27/12/2023 15:35:50
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for customer
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '客户ID',
  `customer_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '客户名称',
  `customer_abbreviation` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '客户简称',
  `owners` json NOT NULL COMMENT '客户所有者集合',
  `viewers` json NULL COMMENT '客户使用者集合',
  `delete_flag` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否删除',
  `creator_id` bigint NOT NULL COMMENT '创建人id',
  `creator_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '创建人名称',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modifier_id` bigint NULL DEFAULT NULL COMMENT '修改人id',
  `modifier_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '修改人名',
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '客户表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of customer
-- ----------------------------
INSERT INTO `customer` VALUES (2, '郭辉', 'gh', '[{\"id\": 116, \"userName\": \"guohui\"}, {\"id\": 2, \"userName\": \"guohui\"}]', '[{\"id\": 116, \"userName\": \"gh\"}]', 0, 116, 'gh', '2023-12-26 14:38:26', 0, '', '2023-12-26 14:38:26');
INSERT INTO `customer` VALUES (3, '五期', 'wq', '[{\"id\": 105, \"userName\": \"patrickasd\"}, {\"id\": 106, \"userName\": \"link liu\"}, {\"id\": 107, \"userName\": \"riku\"}]', NULL, 0, 110, 'zz', '2023-12-27 14:33:42', NULL, NULL, '2023-12-27 14:33:42');
INSERT INTO `customer` VALUES (4, 'xsaxsa', '22', '[{\"id\": 110, \"userName\": \"zz\"}, {\"id\": 108, \"userName\": \"zhuxiong\"}, {\"id\": 106, \"userName\": \"link liu\"}]', '[{\"id\": 110, \"userName\": \"zz\"}, {\"id\": 108, \"userName\": \"zhuxiong\"}, {\"id\": 107, \"userName\": \"riku\"}, {\"id\": 106, \"userName\": \"link liu\"}]', 0, 110, 'zz', '2023-12-27 14:56:52', NULL, NULL, '2023-12-27 14:56:52');

SET FOREIGN_KEY_CHECKS = 1;

查询jsonarray字段owners或viewers中id=116的数据

SELECT c.* FROM price.customer c WHERE JSON_CONTAINS(c.owners, '{"id": 116}', '$') > 0 OR JSON_CONTAINS(c.viewers, '{"id": 1116}', '$') > 0;

修改jsonarray字段viewers中id=116的userName数据

UPDATE price.customer
SET viewers = (
    SELECT JSON_ARRAYAGG(JSON_SET(value, '$.userName', '郭辉修改'))
    FROM JSON_TABLE(
        viewers,
        '$[*]' COLUMNS (
            value JSON PATH '$'
        )
    ) AS json_data
)
WHERE JSON_CONTAINS(viewers, '{"id": 116}', '$') > 0;