0

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

Bertrand
  • 1,840
  • 14
  • 22
  • Presumably for query #3 you want to return everything other than id=1? – Nick Jan 18 '19 at 03:45
  • How do you expect the results to differ from between query#2 and query#3? They are essentially the same thing – Nick Jan 18 '19 at 03:52
  • @Nick you're right. I've added the third test while writing and did not update my question accordingly. I mean the fourth query of course which is returning whole fields. Thank you for pointing it. – Bertrand Jan 18 '19 at 08:13
  • So what result do you want for query 4? and are the results for the other queries what you want? – Nick Jan 18 '19 at 08:15
  • I'm looking for a way to get the fourth result set without the need to add the additional OR condition in the query – Bertrand Jan 18 '19 at 08:27
  • So you want the result for query 4 from either of queries 2 or 3? – Nick Jan 18 '19 at 08:28
  • That's exactly the point – Bertrand Jan 18 '19 at 08:29

1 Answers1

1

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

Nick
  • 138,499
  • 22
  • 57
  • 95