APixel Visuals APixel Visuals - 3 years ago 264
JSON Question

Getting a value from a JSON string in purely MySQL

I'm looking for a single query that's purely MySQL. The goal of this query is to utilize things such as

, or whatever it needs to, in order to find a value in a string.

Let's say that the string looks something like this:

My goal is to get the value of
, in this case,
. However,
might not always be in that location in the string. Neither would any of the other properties. On top of that, the value might not always be
. I need some way to check what number comes after
but before the
. Is there any way of doing this?

Answer Source

This one ?

create table sandbox (id integer, jsoncolumn varchar(255));
insert into sandbox values (1,'{"name":34,"otherName":55,"moreNames":12,"target":26,"hello":56,"hi":26,"asd":552,"p":3722,"bestName":11,"cc":6,"dd":10}');

mysql root@localhost:sandbox> SELECT jsoncolumn->'$.target' from sandbox;
|   jsoncolumn->'$.target' |
|                       26 |


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download