Mysql select json array value

Mysql introduces many features to work and manipulate JSON since version 5.7.8.

We will simulate the options to search empty arrays.
For that, we use MYSQL 8.

We have this table and data below:

CREATE TABLE `courses` (
  `id` int NOT NULL AUTO_INCREMENT,
  `data` json NOT NULL,
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB;INSERT INTO `courses` (`data`) VALUES 
('{"name": "Medicine", "codes": ["3064", "3065"]}'),
('{"name": "Dentistry", "codes": ["3070"]}'),
('{"name": "Pharmacology", "codes": []}'),
('{"name": "Toxicology", "codes": []}');

JSON_EXTRACT

SELECT data FROM courses WHERE JSON_EXTRACT(data, '$.codes') = '[]';

This supposes to return the Pharmacology and Toxicology courses, in fact, return empty results.

There is a trick to using JSON_EXTRACT to cast the comparable the object JSON_ARRAY

SELECT data FROM courses WHERE JSON_EXTRACT(data, '$.codes') = JSON_ARRAY();

Bingo! The result was as expected!

+---------------------------------------+
| data                                  |
+---------------------------------------+
| {"name": "Pharmacology", "codes": []} |
| {"name": "Toxicology", "codes": []}   |
+---------------------------------------+
2 rows in set (0.01 sec)

JSON_LENGTH

SELECT data FROM courses WHERE JSON_LENGTH(data, '$.codes') = 0;

The result as expected, only return records with empty codes

+---------------------------------------+
| data                                  |
+---------------------------------------+
| {"name": "Pharmacology", "codes": []} |
| {"name": "Toxicology", "codes": []}   |
+---------------------------------------+
2 rows in set (0.01 sec)

Inline path operator ->>

SELECT data FROM courses WHERE data->>"$.codes" = '[]';

The result as expected, only return records with empty codes

+---------------------------------------+
| data                                  |
+---------------------------------------+
| {"name": "Pharmacology", "codes": []} |
| {"name": "Toxicology", "codes": []}   |
+---------------------------------------+
2 rows in set (0.01 sec)

Conclusion

JSON type is a really welcome feature that missing at Mysql, also there are some tricks to use correctly the filters and common functions.
Also, keep in mind to search for documents with the good performance you may need to create a virtual index.

References:

• https://www.mysqltutorial.org/mysql-json/
• https://blog.digital-craftsman.de/select-for-empty-json-array-in-mysql/

i'm trying to find out if there is a row which contains a specific date inside a JSON array

Let's say my data looks like this:

Table applications:

id | application_id | data

# Rows
1 | 1 | [{"data" : ["some", "data#1"], "date": "2016-04-21"}, {"data" : ["other", "data#1"], "date" : "2016-04-22"}]
2 | 2 | [{"data" : ["some", "data#2"], "date": "2016-04-21"}, {"data" : ["other", "data#2"], "date" : "2016-04-26"}]
3 | 1 | [{"data" : ["some", "data#3"], "date": "2016-04-22"}, {"data" : ["other", "data#3"], "date" : "2016-04-26"}]
4 | 3 | [{"data" : ["some", "data#4"], "date": "2016-04-26"}]

How can I find all the applications whose data contains the date '2016-04-26'?

So basically I can do this:

select id, json_extract(`data`, "$[*].date") from applications

Which returns:

1 | ["2016-04-21", "2016-04-22"]
2 | ["2016-04-21", "2016-04-26"]
3 | ["2016-04-22", "2016-04-26"]
4 | ["2016-04-26"]

But if try to use json_extract in the WHERE clause I can only use it if I explicitly tell the array's key in the json_extract's path argument, like so:

select * from applications where json_extract(`data`, "$[0].date") = "2016-04-26"

which correctly returns the row with id 4.

But if I try to use a wildcard in the path then it no longer works:

select * from applications where json_extract(`data`, "$[*].date") = "2016-04-26"

this should return the rows 2, 3, 4.

I tried many other options/variations but I can't seem to find a way to structure the query correctly.

Is something like this even possible with the current implementation of MySQL JSON?

How do I query a JSON column in MySQL?

How do I access JSON in MySQL?

What is JSON extract () function in MySQL?

What is JSON extract?