MySQL JSON_SEARCH for multiple arguments of nested objects

Solution 1:

The solution for MySQL 8.0.21:

SELECT template.id
FROM template
CROSS JOIN JSON_TABLE( template.TemplateData,
                       "$[*]" COLUMNS( type VARCHAR(254) PATH "$.type",
                                         id INT PATH "$.id" )
                     ) AS jsontable
WHERE jsontable.type = 'template'
  AND jsontable.id = 1;

fiddle

If template objects may be duplicated in separate value then add DISTINCT.

Any suggestion in regard of MariaDB?

Draft solution applicable to MariaDB.

WITH RECURSIVE
cte1 AS ( SELECT MAX(LENGTH(TemplateData) - LENGTH(REPLACE(TemplateData, '{', ''))) max_obj_count
          FROM template ),
cte2 AS ( SELECT 1 num
          UNION ALL
          SELECT num + 1
          FROM cte2
          WHERE num < ( SELECT max_obj_count
                        FROM cte1 ) )
SELECT DISTINCT
       template.id
FROM template
CROSS JOIN cte2
WHERE LOCATE('"type":"template"' ,SUBSTRING_INDEX(SUBSTRING_INDEX(template.TemplateData, '}', cte2.num), '{', -1))
  AND LOCATE('"id":1' ,SUBSTRING_INDEX(SUBSTRING_INDEX(template.TemplateData, '}', cte2.num), '{', -1))

The problem - this code searches for '"type":"template"' and '"id":1' substrings strictly - i.e. it will not find the rows where the value is written as, for example, '"type" : "template"' (excess space chars) or '"id":"1"' (the value is quoted).

If you want to eliminate this problem then you must get SUBSTRING_INDEX(SUBSTRING_INDEX(template.TemplateData, '}', cte2.num), '{', -1) in one more CTE, clear it from all []{} chars, then wrap with {} and process this value in WHERE as JSON object.