MySQL getting JSON value

Question

In my MySql players table I have a column called achievements and it is a text field which in this particular row has this value:

[  
    {  
        "value":11,
        "globalID":23000000
    },
    {  
        "value":11,
        "globalID":23000001
    },
    {  
        "value":11,
        "globalID":23000002
    },
    ...
    {  
        "value":6044730,
        "globalID":23000065
    }
]

Near the bottom of the array you can see this object:

{  
    "value":48,
    "globalID":23000062
},

I need to be able to parse the value field and show it as a warhero field. But how can I do this? The globalID will stay the same but the value changes. And because the globalID is after the value value I can't use what was used in this post: https://stackoverflow.com/a/21596032/4942382

What SQL query would I need to run to get that value?

Thanks!

You need just one value from that list? Or why do you highlight a particular object? You have the globalID up-fromt, and want to extract the corresponding value? — trincot
– trincot, Commented Nov 1, 2015 at 18:44
@trincot yes I wanna be able to extract the value for the corresponding globalID but without having to fetch the entire field value — user4942382
– user4942382, Commented Nov 1, 2015 at 20:23
MySQL has no idea what JSON is, to the database it's just a text column, and by necessity the entire column must be fetched to be parsed. If you're doing heavy JSON data manipulation like this, keep in mind that PostgreSQL supports native JSON data types and has operators to extract elements like this. — tadman
– tadman, Commented Nov 2, 2015 at 17:25
It is one of the basics to normalise a database, and the very first step is to eliminate non-atomic values, as is the case here. If the design is wrong everything else becomes difficult. — trincot
– trincot, Commented Nov 2, 2015 at 17:30

trincot · Accepted Answer · 2015-11-02 23:07:34Z

0

The design of a table does not even meet the first normalisation level if it stores a non-atomic value in a single column, which is the case with this type of JSON encoded values.

Now if you have no access to the JSON functions available to MySql version 5.7+, and your globalID has a fixed number of digits, then you could do some string matching as follows.

For example, if you need the value that goes with globalID 23000062, then you could do this:

SELECT  players.*,
        CAST(
            SUBSTRING_INDEX(
                SUBSTRING_INDEX(achievements, '"globalID":23000062', 1),
                '"value":',
                -1
            )
        AS UNSIGNED) AS json_extracted_value
FROM    players
WHERE   INSTR(achievements, '"globalID":23000062') > 0

But really, you should seriously consider redesigning your database.

edited Nov 2, 2015 at 23:07

answered Nov 2, 2015 at 21:04

trincot

357k38 gold badges282 silver badges338 bronze badges

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

Comments

meda · Accepted Answer · 2015-11-02 17:34:08Z

0

You should have never saved the JSON in your table, you already broke the rules of relational database design.

mysql cannot make sense of the data, so no SQL query will help you, you have 2 options:

Fix your database design, create tables to hold that data instead of JSON
Fetch the data, decode the JSON, and do all type of manipulations and hacks to get your desired value.

answered Nov 2, 2015 at 17:34

meda

45.5k14 gold badges96 silver badges123 bronze badges

2 Comments

user4942382 Over a year ago

MySql 5.7 supprorts JSON.

meda Over a year ago

my point was it is not use JSON as the native way of storing data, Interesting though as I was not aware of that but still not a good idea. anyway I just looked it up and found this dev.mysql.com/doc/refman/5.7/en/…

Collectives™ on Stack Overflow

MySQL getting JSON value

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related