PHP MySQL JSON UPDATE

Question

I am having trouble updating 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 2017_assessment_data has one record with student_id field (value of 3531) and the activities JSON field with the following data:

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

I've added an empty activity at the bottom of the JSON data, but I can't later edit the values.

Here is my PHP code:

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


    ";

Any ideas??

Nick · Accepted Answer · 2018-05-02 22:09:35Z

1

This can be done using the MySQL JSON functions. It's a slight variation on your query:

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

The differences:

In the WHERE clause, we check for the existence of a 'NEW' value using JSON_SEARCH and using REGEXP make sure that it matches the expected path.
In the SET clause, we use the same JSON_SEARCH to get the path to the value to be updated. Note that we have to TRIM the leading and trailing double-quotes from this value as they are invalid in a path string.

The easiest way to generate this query in PHP is with HereDoc syntax, as it minimises otherwise necessary quoting and escaping. For example:

$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;

Output:

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

edited May 2, 2018 at 22:09

answered May 2, 2018 at 8:33

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Paul Flahr Over a year ago

Works like a charm in MYSQL, but how do I get it to work in PHP? The double quotes " are causing issues. I tried escaping them with \ and no joy

Nick Over a year ago

Hi Paul, see my edit. I think that should work for you.

Paul Flahr Over a year ago

Awesome! Never used heredoc, but will be :) It works for me in PHP now. Too bad I have to use a REGEX for this query. Seems that MYSQL JSON functions do not like dealing with arrays of objects - updating or searching. I could just use a TEXT field with JSON and use REGEX.

Nick Over a year ago

Hi Paul, I was being paranoid using a REGEXP, if you're certain you won't get a field which contains 'NEW' in it elsewhere in the JSON then you could simply change the REGEXP ... to an IS NOT NULL

Collectives™ on Stack Overflow

PHP MySQL JSON UPDATE

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related