2

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

Improve this question
1
  • 1
    If this is a common action, the value should be in its own column. Commented Feb 2, 2022 at 23:57

1 Answer 1

Reset to default
3

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

Improve this answer
1
  • What if we don't have static/certain column names/keys? Can we make a dynamic structure kind of loop or something? Commented Dec 11, 2023 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.