APixel Visuals APixel Visuals - 4 months ago 35
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

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

Let's say that the string looks something like this:
{"name":34,"otherName":55,"moreNames":12,"target":26,"hello":56,"hi":26,"asd":552,"p":3722,"bestName":11,"cc":6,"dd":10,}


My goal is to get the value of
target
, in this case,
26
. However,
"target":26
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
26
. I need some way to check what number comes after
"target":
but before the
,
after
"target":
. 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 |
+--------------------------+

https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html

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