I have a problem extracting data in the JSON column. I'm new to this JSON method, unlike relation tables.
Sample Table: every minute/second all websites activity will be saved in 1 column alongside timestamp.
Expected Table: I wanted to produce is to extract the object details of a specific id, so I can have a full table of that objects.
SELECT dtime, activity.id, activity.ssl, activity.online, activity.cert FROM logs WHERE activity.id = 3dbfiddle:
//dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=7982cd1738b4fc594fb033403a492a6c
asked Feb 2 at 19:01
スペースタイムスペースタイム
411 silver badge6 bronze badges
1
Finally, I found the problem.
- Make sure you check the version if JSON functions are supported.
- MySQL8+
- MariaDB 10.6.0+
In my case, this was easily fixed by JSON_TABLE just like this post "read json array on mysql query"
SELECT dtime, get_activity.* FROM logs, JSON_TABLE( activity, '$[*]' COLUMNS ( `id` int(11) PATH '$.ID', `ssl` int(1) PATH '$.SSL', `online` int(1) PATH '$.Online', `cert` text PATH '$.Cert' ) ) get_activity WHERE get_activity.id = 3;All Result:
Result using WHERE filter:
dbfiddle
answered Feb 3 at 18:10
スペースタイムスペースタイム
411 silver badge6 bronze badges
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:
- //www.mysqltutorial.org/mysql-json/
- //blog.digital-craftsman.de/select-for-empty-json-array-in-mysql/