MySQL: JSON_EXTRACT object from array where 2 conditions are true

Question

I have a JSON column that stores an array of objects, each object having 2 properties:

[
    {
        "title" : "CALCULATED",
        "applies" : true
    },
    {
        "title" : "APPROVED",
        "applies" : false
    },
    {
        "title" : "RECIEVED",
        "applies" : false
    },
]

I want to SELECT all records where one of the objects matches a specific title and applies equals true.

What I've tried:

SELECT 
    col 
FROM 
    table 
WHERE 
    JSON_EXTRACT(col, '$[*].title') = "APPROVED" AND 
    JSON_EXTRACT(col, '$[*].applies');

This returns all records that have an object with title = "APROVED" and applies = true, but independent of each other. In the array I wrote as an example, it would return true because there is an object with title = "APPROVED" and there is another object with applies = true. I want to get all records where both are true on the same object.

Answer 1

You need to convert JSON array column to rows using JSON_TABLE then apply your condition in where clause :

SELECT t.*
FROM mytable t
JOIN  JSON_TABLE(
         col,
         "$[*]"
         COLUMNS(
           title varchar(20) PATH "$.title",
           applies boolean PATH "$.applies"
         )
       ) data
 where title = 'APPROVED' and applies;

Demo here