Extract JSON object inside of an JSON array then search base on ID
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
Finally, I found the problem.
- Make sure you check the version if JSON functions are supported.
- MariaDB 10.6.0+
In my case, this was easily fixed by
JSON_TABLEjust like this post https://stackoverflow.com/a/63001045/9682185
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;
Result using WHERE filter: