Is json an array mysql?

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 = 3 

dbfiddle:

https://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.

1. 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:

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

Is JSON same as array?

Can a JSON value be an array?

What is JSON data type in MySQL?

Can JSON be stored in MySQL?