PHP MySQL JSON Query

Question

J'ai des difficultés à interroger les données JSON de MySQL5.7.x.

J'ai le JSON suivant stocké dans le champ activities . Je veux rechercher les activités dont l'outcome_id = 418.

Ma table contient un enregistrement avec student_id avec une valeur de 10 et le champ activities champ.

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

J'ai le code PHP suivant mais je n'obtiens aucun résultat.

$query = ("SELECT 

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

    FROM table WHERE

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

Avez-vous une idée de ce que je fais de travers ?

Answer 1

Je présume que la sortie souhaitée de cette requête est "Quiz from pages 100-102" .

Votre requête ne fonctionne pas comme vous le pensez : JSON_EXTRACT retournera un tableau de tous les outcome_id en utilisant le chemin que vous avez spécifié (dans ce cas, ["418", "419", "420", "600", "518", "519"] ). Vous pouvez utiliser JSON_SEARCH pour trouver uniquement les activités qui ont un outcome_id de 418 comme ça :

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

qui retournera une valeur de chaîne de caractères de "$.activities[0].outcomes[0].outcome_id" mais il n'y a pas de moyen auquel je pense pour obtenir la [0] l'index des activités dans votre SELECT où vous voulez vraiment SELECT activities->'$.activities[0].activity' .

Globalement, je ne pense pas que vous puissiez y parvenir en utilisant les fonctions JSON intégrées de MySQL sans passer par une procédure stockée. Votre meilleure stratégie serait d'utiliser

SELECT activities FROM table WHERE student_id = 10

et post-traiter le résultat dans votre PHP (je suppose que vous décodez le JSON dans une variable appelée $json ):

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

Sortie :

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