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



  • 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 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;
    

    All Result:

    enter image description here

    Result using WHERE filter:

    enter image description here

    https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=dffc098c0ec9711d391abdd7289af441




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2