Extract JSON object inside of an JSON array then search base on ID

Question

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. enter image description here

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 

enter image description here

dbfiddle:

https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=7982cd1738b4fc594fb033403a492a6c

• Rick James: If this is a common action, the value should be in its own column.

Answer 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:

enter image description here

Result using WHERE filter:

enter image description here

dbfiddle

• menoktaokan: What if we don't have static/certain column names/keys? Can we make a dynamic structure kind of loop or something?