CODEWITHSUNDEEP
mysqlsqlmysql-json
Manigandan Max
Manigandan Max

Reputation: 9

How to use json column (array of object) in the WHERE clause as a condition

I have a student table

ID |  Students  |  Date             | marks
---+-------------------+-------------------+---------------------
1  | Student 1  | 2020-01-01T09:30  | [{"name": "Aptitude", "marks": 6, "total_marks": 20}]

2  | Student 2  | 2020-01-02T10:55  | [{"name": "Aptitude", "marks": 3, "total_marks": 03}]

select this row based on the key-value pair using the WHERE clause. How can I do that?

ID |  Students  |  Date             | marks
---+-------------------+-------------------+-----------------------------------------------
1  | Student 1  | 2020-01-01T09:30  | [{"name": "Aptitude", "marks": 6, "total_marks": 20}]

I tried this

select * from student where JSON_VALUE(marks,'$.[0].marks') > 4

Upvotes: 0

Views: 934

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562320

The JSON path expression '$.[0].marks' is not valid. It would be '$[0].marks' if you're only interested in the first element of the array.

But I assume you really want to check if any marks in the JSON array has a value greater than 4, not just the [0] element of the array.

create table student ( id int primary key, students text, date datetime, marks json);

insert into student values
(1, 'Student 1',  '2020-01-01T09:30', '[{"name": "Aptitude", "marks": 6, "total_marks": 20}]'),
(2, 'Student 2',  '2020-01-02T10:55', '[{"name": "Aptitude", "marks": 3, "total_marks":  3}]');

select * from student
cross join json_table(marks, '$[*]' columns (
    name text path '$.name',
    marks int path '$.marks',
    total_mark int path '$.total_marks'
  )) as j
where j.marks > 4;

Note also that 03 is not a valid integer in JSON. I had to edit it to be 3.

Upvotes: 3

Related Questions