PHP Developer News

JSON Paths and the MySQL JSON Functions

I wrote MySQL and JSON: A Practical Programming Guide to help developers find their way around the MySQL JSON data type and the supporting functions. The MySQL Documentation on the subject is very good but I had to puzzle through the examples to see how things worked.  I might be a bit 'thick' but good examples always make things easier.  Others seem to have similar difficulties.

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 DocumentSo 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.

Most Popular in Database