1

I have a table called "completedgames" it has two columns "GameID" and "gameresults"

I have this JSON inside the "gameresults" column

{
    "gameID": "acaa2a99-a24c",
    "scores": [
        {
            "id": "11888572",
            "seen": false,
            "score": 10
        },
        {
            "id": "14916624",
            "seen": false,
            "score": 70
        }
    ]
}

I need to set "seen" to true at a certain ID

I have come really close and tried many things and read a lot of documentation. The closest I have come is using this:

UPDATE completedgames SET gameresults = JSON_SET(gameresults, '$.scores[*].seen', true) WHERE GameId = 'acaa2a99-a24c' AND JSON_EXTRACT(gameresults, '$.scores[*].id') = '14916624';

but it does not work however if I specify the index that I know corresponds like this

UPDATE completedgames SET gameresults = JSON_SET(gameresults, '$.scores[1].seen', true) WHERE GameId = 'acaa2a99-a24c' AND JSON_EXTRACT(gameresults, '$.scores[1].id') = '14916624';

it works

I know I'm missing something small but I just can't seem to get it right.

Indra Kumar S
  • 2,818
  • 2
  • 16
  • 27
Destin Fritz
  • 25
  • 3
  • Apart from any json solution to this problem, you should consider storing your gameresults in a homonimous table and add a reference in your current table. In this way, any operation, including this one, would be cleaner to be carried out and likely to have better performance with indices. – lemon Apr 05 '23 at 17:11
  • I don't know what you mean at all because I'm very new to this but I need JSON data because I'm using this data to create an instance of a class in a C# script. – Destin Fritz Apr 05 '23 at 17:14

1 Answers1

1

You can't use a JSON path with wildcards in a JSON_SET() function.

https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-set

... an error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard.

So you can change only one value at a time with JSON_SET(). I assume you have a variable number of entries in the JSON array, it isn't always two as in your example. If you want to change all of them, you'd have to write a JSON_SET() with multiple paths.

JSON_SET(gameresults, '$.scores[1].seen', true, '$.scores[2].seen', true, ...)

Another solution would be to fetch the whole JSON document into your application, modify it however you want using client code, and then post then whole JSON document back to the database.

It would be much easier if you did not store your data in JSON format. Store them in a second table with one row per score, using normal columns, not JSON. Then you can use very simple SQL to update them:

UPDATE gamescores SET seen = true 
WHERE gameId = 'acaa2a99-a24c' AND scoreId = '14916624';

You can store data in normal rows and columns, but still format a JSON response when you want to return them to your C# application. Read about JSON_ARRAYAGG() and JSON_OBJECT() functions.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I was able to solve this by fetching the whole JSON document into my application(which was already doing anyway). It makes much more sense to do it this way because I want the client to let the database know if it has already seen this information. When the application is loaded the client will search all completed games to see if their client id is present and if they have already seen it. If there is new information it will be displayed to the client. Now I have this working nicely. Thanks alot! – Destin Fritz Apr 05 '23 at 18:14