PHP MySQL JSON 更新

Question

我在更新 MySQL5.7.x JSON 数据时遇到问题。

我在现场事件中存储了以下 JSON。我想搜索 outcome_id = 418 的事件

我的表 2017_assessment_data 有一条包含 student_id 字段(值为 3531)和包含以下数据的事件 JSON 字段的记录:

  {
      "course": "ENGLISH",
      "level" : "2",
      "activities": [
        {
          "comments": "test1",
          "outcomes": [
            {
              "outcome_id": "423",
              "course": "ENGLISH",
              "course_level": "2",
              "internal_outcome_id": "1"
            }
          ],
          "activity_name": "Quiz from chapters 1,2",
          "date_completed": "20180201"
        },
        {
          "comments": "test1 comments",
          "outcomes": [
            {
              "outcome_id": "421",
              "course": "ENGLISH",
              "course_level": "2",
              "internal_outcome_id": "4"
            },
            {
              "outcome_id": "415",
              "course": "ENGLISH",
              "course_level": "2",
              "internal_outcome_id": "5"
            }
          ],
          "activity_name": "Test chapter 4",
          "date_completed": "20180201"
        },
        {
          "comments": "test1",
          "outcomes": [
            {
              "outcome_id": "426",
              "course": "ENGLISH",
              "course_level": "2",
              "internal_outcome_id": "4"
            },
            {
              "outcome_id": "418",
              "course": "ENGLISH",
              "course_level": "2",
              "internal_outcome_id": "3"
            }
          ],
          "activity_name": "Activity",
          "date_completed": "20180201"
        },
        {
          "comments": "",
          "outcomes": [],
          "activity_name": "NEW",
          "date_completed": ""
        }
      ]
    }

我在 JSON 数据的底部添加了一个空事件，但我以后无法编辑这些值。

这是我的 PHP 代码:

$update = "
    UPDATE 2017_assessment_data 

    SET

    assessment_data = JSON_SET(assessment_data,'$.activities.activity_name','NEW ACTIVITY NAME')



    WHERE 

    student_id = '3531' 
    AND 
    JSON_EXTRACT(assessment_data, '$.course') =  'ENGLISH' 
    AND 
    JSON_EXTRACT(assessment_data, '$.level') =  '2'
    AND
    JSON_EXTRACT(assessment_data, '$.activities[*].activity_name') =  'NEW'


    ";

有什么想法吗？

Answer 1

这可以使用 MySQL JSON 函数来完成。您的查询略有不同:

UPDATE 2017_assessment_data
SET assessment_data = JSON_SET(assessment_data, TRIM('"' FROM JSON_SEARCH(assessment_data, 'one', 'NEW')), 'NEW ACTIVITY NAME')
WHERE student_id = '3531' AND 
      JSON_EXTRACT(assessment_data, '$.course') =  'ENGLISH' AND 
      JSON_EXTRACT(assessment_data, '$.level') =  '2' AND
      JSON_SEARCH(assessment_data, 'one', 'NEW') REGEXP '^"\\$\\.activities\\[[0-9]+\\]\\.activity_name"$'

区别:

1. 在WHERE子句，我们检查是否存在 'NEW'使用 JSON_SEARCH 的值并使用 REGEXP确保它符合预期的路径。
2. 在SET子句，我们使用相同的 JSON_SEARCH获取要更新的值的路径。请注意，我们必须 TRIM此值的前导和尾随双引号，因为它们在路径字符串中无效。

在 PHP 中生成此查询的最简单方法是使用 HereDoc语法，因为它最大限度地减少了其他必要的引用和转义。例如:

$sid = 3531;
$course = 'ENGLISH';
$level = 2;
$sql = <<<EOD
UPDATE 2017_assessment_data
SET assessment_data = JSON_SET(assessment_data, TRIM('"' FROM JSON_SEARCH(assessment_data, 'one', 'NEW')), 'NEW ACTIVITY NAME')
WHERE student_id = '$sid' AND 
      JSON_EXTRACT(assessment_data, '$.course') =  '$course' AND 
      JSON_EXTRACT(assessment_data, '$.level') =  '$level' AND
      JSON_SEARCH(assessment_data, 'one', 'NEW') REGEXP '^"\\\\$\\\\.activities\\\\[[0-9]+\\\\]\\\\.activity_name"$'
EOD;
echo $sql;

输出:

UPDATE 2017_assessment_data
SET assessment_data = JSON_SET(assessment_data, TRIM('"' FROM JSON_SEARCH(assessment_data, 'one', 'NEW')), 'NEW ACTIVITY NAME')
WHERE student_id = '3531' AND 
      JSON_EXTRACT(assessment_data, '$.course') =  'ENGLISH' AND 
      JSON_EXTRACT(assessment_data, '$.level') =  '2' AND
      JSON_SEARCH(assessment_data, 'one', 'NEW') REGEXP '^"\\$\\.activities\\[[0-9]+\\]\\.activity_name"$'