mysql-json更新

6ie5vjzr 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(311)

我在更新mysql5.7.xjson数据时遇到问题。
我在fieldactivities中存储了以下json。我想搜索结果为\u id=418的活动
我的表2017\u assessment\u data有一个记录，其中student\u id字段（值为3531）和activities 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'

    ";

有什么想法吗？？

mysql JSON php

来源：https://stackoverflow.com/questions/50127737/php-mysql-json-update

1条答案

按热度按时间

g9icjywg1#

这可以使用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"$'

区别在于：
在 WHERE 子句，我们检查是否存在 'NEW' 价值使用 JSON_SEARCH 以及使用 REGEXP 确保它与预期路径匹配。
在 SET 子句，我们使用相同的 JSON_SEARCH 获取要更新的值的路径。请注意，我们必须 TRIM 此值的前导双引号和尾随双引号在路径字符串中无效。
在php中生成此查询的最简单方法是使用herdeoc语法，因为它可以最大限度地减少其他必要的引用和转义。例如：

$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"$'

赞(0）回复(0）举报 2021-06-21

我来回答

mysql-json更新

1条答案

相关问题

热门标签

最新问答