How to update nested JSON data type in MySQL 5.7

Question

Using JSON_SET I have no problem updating simple JSON datatype, but with nested it just doesn't work.

How query supposed to look like with format like this:

{  
   "textures":[  
      {  
         "label":"test",
         "types":{  
            "t_1":0,
            "t_2":0
         }
      },
      {  
         "label":"KEK",
         "types":{  
            "t_1":0,
            "t_2":0
         }
      }
   ],
   "weapons":[  
      {  
         "name":"WW_SHT",
         "ammo":0
      },
      {  
         "name":"WW_DSS",
         "ammo":0
      }
   ]
}

Some rows might be empty, some rows won't have "weapons" structure.

What I tried:

UPDATE `player`   SET `info` = COALESCE(
    JSON_MERGE(
      `info`,
      JSON_OBJECT('textures',
        JSON_OBJECT('types',
          JSON_OBJECT('t_1', '1', 't_2', '0')
        )
      )
    ),
    JSON_INSERT(
      JSON_OBJECT(),
      '$.textures',
       JSON_OBJECT('types',
         JSON_OBJECT('t_1', '1', 't_2', '0')
       )
    )
  );

I want to update t_1 to change value from 0 to 1

Dave Stokes · Answer 1 · 2018-09-06T19:49:32.110

2

You are really convoluding your code. Try your update with json_set or json_replace and something like

... WHERE doc->"$.textures[*].types.t_1" = 0

Your code is a little hard to read and comprehend with some many functions embedded together

BTW json_merge is deprecated in MySQL 8

And you will want to read https://elephantdolphin.blogspot.com/2018/09/json-paths-and-mysql-json-functions.html

edited Sep 06 '18 at 19:49

answered Sep 06 '18 at 14:46

Dave Stokes

775
4
10

doesn't work if there is empty field or no textures field – JayJay Sep 26 '18 at 21:02

score 0 · Answer 2 · answered Sep 06 '18 at 21:26

0

Refer to JSON_REPLACE()

UPDATE player SET info = JSON_REPLACE(info, '$.textures[0].t_1', '1');

However, this only replaces the value in the 0th texture. If you have multiple textures, you can find out how many textures:

SELECT JSON_LENGTH(info, '$.textures') AS textures_count FROM player;

P.S.: This is probably an app that would be better off using a traditional normalized database. Actually, I haven't seen any example of JSON in MySQL that wouldn't be easier if the tables were designed in a traditional way.

answered Sep 06 '18 at 21:26

Bill Karwin

538,548
86
673
828

doesn't work if there is empty field or no textures field – JayJay Sep 26 '18 at 21:02
You didn't specify in your question above which fields were subject to be missing. – Bill Karwin Sep 26 '18 at 21:05

How to update nested JSON data type in MySQL 5.7

2 Answers2