Question 272746 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: JSON_EXTRACT object from array where 2 conditions are true

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.

Tags:

2

Answers


  1. 0

    If you just want to get data with title = "APROVED" and applies = true, you can do this:

    SELECT *
FROM your_table
WHERE JSON_CONTAINS(col, '{"title": "APPROVED", "applies": true}', '$');

    I have create https://dbfiddle.uk/hrM80a8c .
    Please check

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search