How to update child elements of a json field in mariadb

Question

I have a table with one column which is a TEXT column but we store json data there. The json data looks like this:

{
  "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": ""
    }
  ]
}

I am trying to update the properties from the individual items in the fields array. So far I have got this query:

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;

But the syntax here is not quite right. Right not getting this error:

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

I can not figure out how to use JSON_TABLE() to update the data. I could not find out anything in the docs as well, all the examples there are for SELECT queries.

Is this something that can be done?

Answer 1

Json table is a source that must be in a FROM clause.

So your query should look like

UPDATE schema_repo sr
SET sr.sr_schema = (
SELECT JSON_SET(
    sr1.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')))
        )
    )
) FROM schema_repo sr1, JSON_TABLE(
    sr1.sr_schema,
    '$.fields[*]' COLUMNS (
        value JSON PATH '$'
    )
) AS field
WHERE sr1.sr_schema = sr.sr_schema);

The WHERE clause at the end must be a primary KEY to identify the correct row