Search for JSON string within text column in MySQL

48
October 20, 2018, at 10:50 AM

I need to find a string within a TEXT column where I stored JSON string.Database Table Structure

I have database column with name attendance_record where I stored json with attributes like att_status , Day , Date Now I need to query for att_status = 0 within attendance_record column mean get all absent students

SELECT * FROM `daywise_student_attendance` WHERE  `attendance_record`.att_status LIKE '%0%'
Answer 1

If you have MySQL 5.7 or later, you can use the inbuilt JSON functions to extract the value of att_status. Based on your data, the appropriate query would be:

SELECT * FROM `daywise_student_attendance` WHERE attendance_record->>'$[0].att_status' = 0

If you don't have MySQL 5.7 or later, you are restricted to using LIKE (or RLIKE), something like this:

SELECT * FROM `daywise_student_attendance` WHERE attendance_record LIKE "%\"att_status\":\"0\"%"
READ ALSO
Add integer value to date SQL

Add integer value to date SQL

In excel with Microsoft query, we have one column which is formatted as date called "JM5OPENORDERSDAY_0CONFIRMED_DEL_DATE"

51
Display date just once for multiple rows in one column Mysql and PHP

Display date just once for multiple rows in one column Mysql and PHP

I need to display date in one column for multiple rows just like that:

37
Build a MySQL instruction for 1000 lines of raw data with code [on hold]

Build a MySQL instruction for 1000 lines of raw data with code [on hold]

I have a two columns in excel to mass update a MySQL serverColumn one is for reference to existing data and column two is new data

17
javascript textarea POST line break MySQL

javascript textarea POST line break MySQL

I need to POST/GET a < textarea > via Javascript to MySQL

66