我在查询mysql5.7.xjson数据时遇到问题。
我在字段中存储了以下json activities . 我想搜索结果为\u id=418的活动
我的table上有一张 student_id 值为10的字段和 activities 现场。

{
  "activities": [
    {
      "activity": "Quiz from pages 100-102",
      "comments": "comments",
      "outcomes": [
        {
          "outcome_id": "418",
          "course": "English",
          "course_level": "2"
        },
        {
          "outcome_id": "419",
          "course": "English",
          "course_level": "2"
        },
        {
          "outcome_id": "420",
          "course": "English",
          "course_level": "2"
        },
        {
          "outcome_id": "600",
          "course": "Science",
          "course_level": "2"
        }
      ],
      "date_completed": "20180102"
    },
    {
      "activity": "Quiz from pages 200-250",
      "comments": "comments",
      "outcomes": [
        {
          "outcome_id": "518",
          "course": "English",
          "course_level": "2"
        },
        {
          "outcome_id": "519",
          "course": "English",
          "course_level": "2"
        }
      ],
      "date_completed": "20180102"
    }
  ]
}

我有下面的php代码，但我没有得到任何结果。

$query = ("SELECT 
    activities->'$.activities[*].activity'
    FROM table WHERE
    JSON_EXTRACT(activities, '$.activities[*].outcomes[*].outcome_id')  = '\"418\"' AND student_id = '10'
    ");

你知道我错在哪里吗？

$selected = array();
foreach ($json->activities as $activity) {
foreach ($activity->outcomes as $outcome) {
if ($outcome->outcome_id == '418') $selected[] = $activity->activity;
}
}
print_r($selected);

1条答案

按热度按时间

gajydyqb1#

我假设这个查询的期望输出是 "Quiz from pages 100-102" .
您的查询没有如您所想的那样工作： JSON_EXTRACT 将返回所有 outcome_id 正在使用指定的路径（在本例中， ["418", "419", "420", "600", "518", "519"] ). 你可以用 JSON_SEARCH 仅查找具有 outcome_id 像这样的418人中：

JSON_SEARCH(activities, 'all', '418', NULL, '$.activities[*].outcomes[*].outcome_id')

返回字符串值 "$.activities[0].outcomes[0].outcome_id" 但我想不出任何办法 [0] 你的活动索引 SELECT 你真正想要的地方 SELECT activities->'$.activities[0].activity' .
总的来说，我不认为不用存储过程就可以使用内置的mysql json函数实现这一点。你最好的策略是

SELECT activities FROM table WHERE student_id = 10

并在php中对结果进行后期处理（我假设您将json解码为一个名为 $json ):

$selected = array();
foreach ($json->activities as $activity) {
    foreach ($activity->outcomes as $outcome) {
        if ($outcome->outcome_id == '418') $selected[] = $activity->activity;
    }
}
print_r($selected);

输出：

Array
(
    [0] => Quiz from pages 100-102
)

展开查看全部

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

mysql-json查询

1条答案

相关问题

热门标签

最新问答