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 · Accepted Answer · 2018-09-06 19:49:32Z

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 6, 2018 at 19:49

answered Sep 6, 2018 at 14:46

Dave Stokes

8235 silver badges11 bronze badges

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

1 Comment

JayJay Over a year ago

doesn't work if there is empty field or no textures field

Bill Karwin · Accepted Answer · 2018-09-06 21:26:32Z

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 6, 2018 at 21:26

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

2 Comments

JayJay Over a year ago

doesn't work if there is empty field or no textures field

Bill Karwin Over a year ago

You didn't specify in your question above which fields were subject to be missing.

Collectives™ on Stack Overflow

How to update nested JSON data type in MySQL 5.7

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related