MySQL JSON列新增数组元素：员工打卡场景的更新实现咨询

针对你需要在JSON列的数组中添加PUNCH_IN和PUNCH_OUT记录的需求，我们可以利用MySQL的JSON_ARRAY_APPEND和JSON_OBJECT函数来实现，同时处理字段为空的边界情况。

核心思路 #

你的目标是往rule_codes字段的emp_sheet数组末尾追加新的打卡对象，所以需要：

1. 处理字段为NULL的情况（第一次打卡时），确保初始JSON结构存在；
2. 用JSON_ARRAY_APPEND指定路径往数组追加元素；
3. 用JSON_OBJECT构造要添加的打卡记录。

打卡上班（PUNCH_IN）的UPDATE语句 #

UPDATE your_table
SET rule_codes = JSON_ARRAY_APPEND(
    -- 如果字段为空，初始化包含空数组的JSON结构
    COALESCE(rule_codes, '{"emp_sheet": []}'),
    -- 指定要追加元素的数组路径
    '$.emp_sheet',
    -- 构造打卡上班的JSON对象
    JSON_OBJECT(
        'rulecode', 'PUNCH_IN',
        'result', 1,
        'applytime', NOW() -- 替换为实际打卡时间，比如传入的参数
    )
)
WHERE emp_id = '目标员工ID'; -- 根据实际业务条件筛选

打卡下班（PUNCH_OUT）的UPDATE语句 #

UPDATE your_table
SET rule_codes = JSON_ARRAY_APPEND(
    COALESCE(rule_codes, '{"emp_sheet": []}'),
    '$.emp_sheet',
    JSON_OBJECT(
        'rulecode', 'PUNCH_OUT',
        'result', 1,
        'applytime', NOW()
    )
)
WHERE emp_id = '目标员工ID';

关键细节说明 #

• COALESCE(rule_codes, '{"emp_sheet": []}')：当rule_codes为NULL时，自动替换为包含空emp_sheet数组的JSON，避免第一次打卡时因路径不存在报错；
• JSON_ARRAY_APPEND：专门用于往JSON数组的末尾追加元素，这正是你需要的"添加新数组元素"的操作，比你尝试的JSON_SET更合适（JSON_SET是设置指定路径的值，而非追加）；
• JSON_OBJECT：快速将键值对组装成JSON对象，完美匹配你需要的打卡记录格式。

效果验证 #

执行PUNCH_IN语句后，rule_codes会变成：

{"emp_sheet":[{"rulecode":"PUNCH_IN","result":1,"applytime":"2024-05-20 09:15:30"}]}

再执行PUNCH_OUT语句后，就会得到你示例中的结构：

{"emp_sheet":[{"rulecode":"PUNCH_IN","result":1,"applytime":"2024-05-20 09:15:30"},{"rulecode":"PUNCH_OUT","result":1,"applytime":"2024-05-20 18:00:00"}]}

内容的提问来源于stack exchange，提问作者Rahul Dadhich