0

In my table I have a JSON field that has an object with key values where some values are arrays. Verifying if a single key exists or even if the key exists with a specified value is easy enough using JSON_CONTAINS_PATH and JSON_EXTRACT respectively, however I need to go one or two steps further.

I need to find an array element (an object) that has a specified key and value while at the same time checking if another specified key doesn't exist or if it exists and has a falsly value.

I've been trying combinations of JSON_CONTAINS_PATH and JSON_EXTRACT and JSON_CONTAINS

For example:

mysql> SET @j = '{"data": [{"a": "abc", "b": true}, {"a": "123"}]}';
mysql> SELECT * FROM db.table WHERE JSON_CONTAINS(@j->>"$.data", JSON_OBJECT('a', 'abc', 'b', true))
+--------------+
| RESULT:    1 |
+--------------+

The above works great, but if I try to apply the same to the second object {"a": "123"} it returns 0. 0 would normally be a valid response but our current usage of "b" is EXISTS && TRUE, EXISTS && FALSE and NOT EXISTS. The first two work with the example above, but I cannot find a way to verify if "a" = "abc" && "b" NOT EXISTS with the current JSON functions provided by MySQL

Also, I tried I also tried JSON_CONTAINS(@j->>"$.data", JSON_OBJECT('a', 'abc', 'b', NULL)) to no avail.

navybofus
  • 59
  • 9
  • 1
    Try [dbfiddle](https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=43ae37e14b125caf0b599385d6a7ec1b). – wchiquito Jul 16 '19 at 20:28
  • Well it took me a while to figure out what all was going on in your fiddle, but now I understand it and the logic behind it. It doesn't work for my case exactly, but I think it's a great starting point for anyone doing complex JSON queries. Thanks. – navybofus Jul 17 '19 at 19:22

0 Answers0