PHP MySQL JSON Query

Question

I am having trouble querying MySQL5.7.x JSON data.

I have the following JSON stored in the field activities. I want to search for activities where the outcome_id = 418

My table has one record with student_id field with value of 10 and the activities field.

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

I have the following PHP code but I am getting no results.

$query = ("SELECT 


    activities->'$.activities[*].activity'

    FROM table WHERE

    JSON_EXTRACT(activities, '$.activities[*].outcomes[*].outcome_id')  = '\"418\"' AND student_id = '10'
    ");

Any ideas on where I am going wrong?

Is your desired output only the activity which has an outcome_id of 418 i.e. "Quiz from pages 100-102"? — Nick
– Nick, Commented Apr 18, 2018 at 7:48

Nick · Accepted Answer · 2018-04-18 08:53:20Z

I'm presuming that the desired output from this query is "Quiz from pages 100-102".

Your query doesn't work as you think: JSON_EXTRACT will return an array of all outcome_id's using the path you specified (in this case, ["418", "419", "420", "600", "518", "519"]). You can use JSON_SEARCH to find only the activities which have an outcome_id of 418 like so:

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

which will return a string value of "$.activities[0].outcomes[0].outcome_id" but there isn't any way I can think of to get the [0] index of activities into your SELECT where you really want SELECT activities->'$.activities[0].activity'.

Overall, I don't think you can achieve this using the built-in MySQL JSON functions without going to a stored procedure. Your best strategy would be to use

SELECT activities FROM table WHERE student_id = 10

and post-process the result in your PHP (I'm assuming you decode the JSON into a variable called $json):

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

Output:

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

Yes, the desired output from this query is "Quiz from pages 100-102". I guess I will have to load the entire JSON document for a student and process the JSON in PHP
If there was a way that you could get individual activities in the database (rather than the activities array you have at the moment) you could make it work in MySQL using the JSON_SEARCH I mentioned.

Collectives™ on Stack Overflow

PHP MySQL JSON Query

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related