CODEWITHSUNDEEP
mysqljsonjson-extract
Bertrand
Bertrand

Reputation: 1976

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) ?

Upvotes: 0

Views: 1029

Answers (1)

Nick
Nick

Reputation: 147266

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

Upvotes: 1

Related Questions