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

Question

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

Are you using MySQL version 8+ ?

Madhur Bhaiya
– Madhur Bhaiya

2018-11-28 12:00:57 +00:00
Commented Nov 28, 2018 at 12:00 — Madhur Bhaiya
– Madhur Bhaiya, Commented Nov 28, 2018 at 12:00
Mysql Version is 5.7

5a01d01P
– 5a01d01P

2018-11-28 12:02:12 +00:00
Commented Nov 28, 2018 at 12:02 — 5a01d01P
– 5a01d01P, Commented Nov 28, 2018 at 12:02

Dave Stokes · Accepted Answer · 2018-11-28 15:23:13Z

1

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)
);

answered Nov 28, 2018 at 15:23

Dave Stokes

8235 silver badges11 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related