CODEWITHSUNDEEP
mysqljsondatabasewhere-clause
5a01d01P
5a01d01P

Reputation: 683

Mysql where clause query on json column in which array of objects data

I have following records in mysql(5.7) table in which data has json data type.

id  data 
1   [{"key": 1,"value": "rom"},{"key": 54,"x": 1}]
2   [{"key": 1,"value": "sun"},{"key": 54,"x": 0.5}]
3   [{"key": 54,"x": 1.2},{"key": 1,"value": "test"}]
4   [{"key": 1,"value": "japan"},{"key": 54,"x": 2}]
5   [{"key": 1,"value": "east"},{"key": 52,"x": 1}]

I want to make where condition on data column. if key = 54 and x >= 1 then it should retrun id => 1,3,4

if key = 54 and x < 1 then it should retrun id => 2

Upvotes: 1

Views: 460

Answers (1)

Dave Stokes
Dave Stokes

Reputation: 823

Dealing with arrays is a little messy in cases like this. You end up having to do positional things like the follow that, while they do work, are not easy to read or modify. JSON objects are a little more flexible.

select id from y where 
(json_contains(doc,"54","$[0].key") or json_contains(doc,'54',"$[1].key")
AND
(json_extract(doc,"$[0].x") >= 1 or json_extract(doc,"$[1].x") >= 1)
);

Upvotes: 1

Related Questions