Writing django query on Json field.

date                    class_name         status(json)

2023-11-10          first                    {1: "p", 2: "a"}
2023-11-11          first                    {1: "a", 2: "a"}
2023-11-12          first                    {1: "p", 2: "p"}

this is my database (mysql).
{1: "p", 2: "a"} -----> where 1 is roll number and (p or a) is present or absent.
I am not able to write a query.
The query is to get the number of classes attended by roll 1,2 or the count of roll’s with “p” between 2 dates, ie start_date=2023-11-10 to end_date=2023-11-12.

1----> 2 (means roll 1 is present for 2 classes between the dates 10 to 12)
2---->1 (means roll 2 is present for 1 classes between the dates 10 to 12)

NOTE) I am using Mysql and status field in Json.
Can anyone please help me with this…

Note: When posting preformatted data (or code), enclose the text between lines of three backtick - ` characters to preserve the formatting. (I’ve taken the liberty of editing your original post for this.)

1 Like