CODEWITHSUNDEEP
mysqlsqljson
S.M_Emamian
S.M_Emamian

Reputation: 17383

How to parse json column in MySQL Version < 5.7

I have a column called inventory.

id ..... inventory

the content of this column is:

{"STC1":{"count":"1"},"STC2":{"count":0}}

the count value is variable. I don't want to use application side, I want to use sql.

for example

what I want:

... where STC1.count > 0

or

... where STC1.count > 1 or STC2.count < 5

Upvotes: 1

Views: 701

Answers (1)

Veve
Veve

Reputation: 6758

You can't, according to the official MySQL website, the JSON support has been introduced for the 5.7.8 version, it isn't available natively for older versions.

For MySQL < 5.7.8, this JSON content is just a string, MySQL has nothing to extract structured data from it.

At best you'll be abble to check WHERE inventory LIKE '%"STC1":{"count":"0"%' to detect the row with a STC1 at 0 or something like that but not much more, and it will rapidly becoming really hairy to do anything complex.

In general it's better to store atomic data to avoid this kind of problem.

Upvotes: 1

Related Questions