How to Retrieve Records from SQL Database with JSON Data Array in a Column Using a MySql Query?

Question

I have a column 'tours' contains JSON data structured as shown below:

[
  {
    "widgets": [
      {
        "name": "Calendar",
        "uuid": "db7308b5-ee0a-46d2-80bb-63dcb57f1152",
        "value": "2023-12-23",
        "system_name": "Calendar"
      },
      {
        "name": "Number",
        "uuid": "7cbdf159-7368-4db8-83e0-775cdd223131",
        "value": 3
      },
      {
        "name": "Number",
        "uuid": "c8caa756-8563-4811-ac86-9dc0860832b4",
        "value": 0
      }
    ]
  }
]

I am trying to construct an SQL query that will allow me to retrieve records based on specific conditions within this JSON structure. Specifically, I want to fetch records where the "widgets" array contains an object with the "name" attribute set to "Calendar" and its associated "value" falls between the dates 2023-12-01 and 2023-12-31.

Could someone assist me in crafting an SQL query to fetch the required records? I would appreciate an example using Laravel's Eloquent query builder.

Answer 1

SELECT test.*
FROM test
CROSS JOIN JSON_TABLE(test.val,
                      '$[0].widgets[0]' COLUMNS ( name VARCHAR(100) PATH '$.name',
                                                  value VARCHAR(100) PATH '$.value'
                                                  )
                      ) jsontable
WHERE name = 'Calendar' AND CAST(value AS DATE) BETWEEN '2023-12-01' AND '2023-12-31';

fiddle