mysql - 如何更新 MySQL JSON 列中的特定值

Question

我正在尝试通过搜索特定的 ID 从 MySQL 列更新 JSON 数组中的特定值，我希望它是动态的而不指定数组键。

这是我尝试过的:

update myTable JSON_REPLACE(`data`, $.OD, '3/1')
where JSON_EXTRACT(`data`, '$.ID') = 'some id';

这是我的列数据:

[
  {"ID":63010092,"IT":"OV80419947-63010092_1_3","OD":"21/10","SU":0,"OR":0},
  {"ID":63010093,"IT":"OV80419947-63010093_1_3","OD":"8/5","SU":0,"OR":1},
  {"ID":63010094,"IT":"OV80419947-63010094_1_3","OD":"8/5","SU":0,"OR":2}
]

Answer 1

尝试(调整您认为必要的所有内容):

UPDATE `myTable`
  INNER JOIN JSON_TABLE(
      `myTable`.`data`,
      '$[*]'
      COLUMNS(
        `position` FOR ORDINALITY,
        `ID` INT PATH '$.ID'
      )
    ) `der`
SET `data` = JSON_REPLACE(
  `myTable`.`data`,
  CONCAT('$[', `der`.`position` - 1, '].OD'),
  '3/1'
)
WHERE `der`.`ID` = 63010092;

参见dbfiddle .