如何在MariaDB中更新JSON字段的子元素

场景说明 #

表schema_repo的sr_schema列是TEXT类型，实际用于存储JSON数据，JSON结构示例如下：

{
  "version": "5",
  "ws_version": "5",
  "user": "XXXX",
  "fields": [
    {
      "crm_attribute": "PREFECTURE_CODE",
      "sp_attribute": "PREFECTURE_CODE",
      "process_type": "field",
      "in_data_type": "String",
      "out_data_type": "String",
      "array": "0",
      "default_value": ""
    },
    {
      "crm_attribute": "EDUCATION_LEVEL",
      "sp_attribute": "EDUCATION_LEVEL",
      "process_type": "field",
      "in_data_type": "String",
      "out_data_type": "String",
      "array": "0",
      "default_value": ""
    }
  ]
}

尝试的SQL语句 #

为了更新fields数组中单个项的属性，编写了如下SQL，但存在语法问题：

UPDATE schema_repo sr
SET sr.sr_schema = JSON_SET(
    sr.sr_schema,
    '$.fields',
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'array', JSON_UNQUOTE(JSON_EXTRACT(field.value, '$.array')),
            'crm_attribute', TRIM(BOTH ' ' FROM JSON_UNQUOTE(JSON_EXTRACT(field.value, '$.crm_attribute'))),
            'default_value', JSON_UNQUOTE(JSON_EXTRACT(field.value, '$.default_value')),
            'in_data_type', JSON_UNQUOTE(JSON_EXTRACT(field.value, '$.in_data_type')),
            'out_data_type', JSON_UNQUOTE(JSON_EXTRACT(field.value, '$.out_data_type')),
            'process_type', JSON_UNQUOTE(JSON_EXTRACT(field.value, '$.process_type')),
            'sp_attribute', TRIM(BOTH ' ' FROM JSON_UNQUOTE(JSON_EXTRACT(field.value, '$.sp_attribute')))
        )
    )
), JSON_TABLE(
    sr.sr_schema,
    '$.fields[*]' COLUMNS (
        value JSON PATH '$'
    )
) AS field;

报错信息 #

执行上述SQL时出现语法错误：

SQL Error [1064] [42000]: (conn=8976) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(
    sr.sr_schema,
    '$.fields[*]' COLUMNS (
        value JSON PATH '$...' at line 16

疑问 #

官方文档中仅提供了JSON_TABLE()用于SELECT查询的示例，不清楚如何用它实现数据更新。请问更新fields数组中单个项属性的需求是否可以实现？

内容的提问来源于stack exchange，提问作者Sayantan Das