MySQL accessing a json array

Question

Need help figuring out syntax for an MySQL query.

I have a table called "activegames" with a column called "gameresults"

inside game results I have this JSON data.

{
    "gameID": "7c3b0c36-c18e",
    "scores": [
        {
            "id": "14916624",
            "score": 40
        },
        {
            "id": "1234565",
            "score": 30
        }
    ]
}

I have tried many things and have read a lot of documentation but I cannot figure how to select the data inside scores and say get each id or each score.

Can anyone help me figure out the syntax for the query.

EDIT: I also need to figure out how I can search the "scores" array to see if any of the elements has a specific id'

something like SELECT gameresults WHERE scores CONTAINS ID "1234565"

basically I have a php script that I need to pass an id to the query and search the whole "activegames" table to see if any of the "gameresults" has a "scores" array containing that id.

Answer 1

You can do it using json_table to convert your json into rows :

SELECT *
     FROM
       activegames, JSON_TABLE(
         gameresults,
         "$.scores[*]"
         COLUMNS(
           id INT PATH "$.id",
           score INT PATH "$.score"
         )
       ) data
where id = 14916624

Demo here