 |
| MySQL and JSON a Practical Programming Guide should be on your desk as a handy reference to MySQL's JSON data type. |
There was a recent post on Stackoverflow.com where someone had this JSON document:
{
"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
}
]
}
And they wanted want to update t_1 to change value from 0 to 1. I will not repost their code but to my eyes it looked convoluted.
So How Do You Get There From Here?
Trying to figure out how to get down to a key or value is easy. To see the top level keys simply use JSON_KEYS()SELECT JSON_KEYS(doc) FROM zz1 LIMIT 1;
'[\"weapons\", \"textures\"]'
But how to get deeper??
By using select doc->>"$.textures[*]" from zz1 limit 1; we get all the info under the textures key.
[{"label": "test", "types": {"t_1": 0, "t_2": 0}}, {"label": "KEK", "types": {"t_1": 0, "t_2": 0}}]
Okay so we are getting closer to the target! Now to take one more step closer with select doc->"$.textures[*].types" from zz1 limit 1;
[{"t_1": 0, "t_2": 0}, {"t_1": 0, "t_2": 0}]
I like to use JSON_PRETTY to get a enhanced view:
select json_pretty(doc>"$.textures[*].types")
from zz1 limit 1;
[
{
"t_1": 0,
"t_2": 0
},
{
"t_1": 0,
"t_2": 0
}
]
But there are two t_1s!
The next step is to get just those t_1 values and that is done with select
doc->"$.textures[*].types.t_1" from zz1;
Which gives us:
[0, 0]
Not really confidence inspiring ,eh? So lets change one of those zeros to a nine.
update zz1 set doc = json_set(doc,"$.textures[0].types.t_1",9);
So did we change the first or the second t_1??
select json_pretty(doc>"$.textures[*].types")
from zz1 ;
[
{
"t_1": 9,
"t_2": 0
},
{
"t_1": 0,
"t_2": 0
}
]
But lets double check and change the second t_1 also.
update zz1 set doc = json_set(doc,"$.textures[1].types.t_1",7) ;
Hopefully that second one will end up with a value of seven.
select
json_pretty(doc->"$.textures[*].types") from zz1;
[
{
"t_1": 9,
"t_2": 0
},
{
"t_1": 7,
"t_2": 0
}
]
So now we can get to the exact values we want.
Annotated JSON Document
So lets look at the section of the JSON document and annotate in red the paths in the document."textures":[
{ -- textures[0]
"label":"test",
"types":{ -- textures[0].types
"t_1":0,--textures[0].types.t_1
"t_2":0
}
},
{ -- textures[1]
"label":"KEK",
"types":{
"t_1":0,--textures[1].types.t_1
"t_2":0
}
}
]
Hopefully this will ease someone's confusion down the line. And please do buy my book.
