Search code examples
mysqljsonjson-extract

Mysql JSON_EXTRACT ignore some fields when doing 'not matching' requests

I went into some trouble when performing some select JSON_EXTRACT requests on JSON data stored in a Mysql database.

Each row doesn't have exactly the same JSON data structure. All is going well when I'm using JSON_EXTRACT to select fields matching a condition.

The problem is when trying to select fields that are not matching the condition. Only fields which does have the key (though not matching data of course) are returned.

You'll find a fiddle here that reproduces this behavior.

I think it's an intended thing but I wonder if there is a sugar workaround that can lead to the fiddle's fourth request result without adding another condition (in the real case, the requests are programmatically generated based on a specific API syntax and adding contextual conditions will be a pain) ?

Solution

  • One way around your problem is to select id's which match the expression, and then use them in an IN or NOT IN expression dependent on whether you want to check for a match or non-match e.g.

    SELECT *
FROM `test`
WHERE id IN (SELECT id
             FROM `test` 
             WHERE data->>'$.test' = 'passed');

    or

    SELECT *
FROM `test`
WHERE id NOT IN (SELECT id
                 FROM `test` 
                 WHERE data->>'$.test' = 'passed');

    The only difference in the queries is the addition of the word NOT to negate the match.

    Demo