MySQL JSON_SEARCH for multiple arguments of nested objects

Question

I have a db-table containing json formated strings:

CREATE TABLE `template` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `TemplateData` longtext NOT NULL,
  PRIMARY KEY (`Id`)
);

INSERT INTO template (Id, TemplateData) VALUES
(1, '[]'),
(2, '[{"type":"template","id":1}]'),
(3, '[{"type":"other", "id":1}]'),
(4, '[{"type":"template","id":3},{"type":"template","id":1}]'),
(5, '[{"type":"template","id":2}]');

http://sqlfiddle.com/#!9/739f3a

For background: these records are templates for a frontend to build dynamic views. Every template is able to include another template. So based on above data, record #2 is a template using the other template #1 inside. View it like reusable parts.

Inside the json I have an array containing multiple types of objects. In my example are two different variants: {type: "template", id: number} and {"type": "other", "id": number}.

Server-Architecture

Production:

MySQL Server Version 8.0.21.

Development:

MariaDB Server Version 10.4.11

What i want to retrieve by SELECT

I need a list of all templates, which are using a specific other template. I want to select all records, which contain an object of $[*].type='template' AND $[*].id=1.

• Based on the given records, i want to retrieve rows #2 and #4, because both contain an object matching both arguments. Complication is on #4, having the record at array index 1.
• I don't want #1 because no element inside the array
• I don't want #3 because $[0].type is not template

What I already tried

I made some tryouts using JSON_SEARCH() and JSON_EXTRACT(), but could not handle to get my expected rows:

SELECT
    Id,
    JSON_EXTRACT(TemplateData,
                 JSON_UNQUOTE(
                 REPLACE(JSON_SEARCH(TemplateData,
                                     'all',
                                     'template'),
                         '.type"',
                         '.id"'))) AS includedTemplateId 
FROM    template 
HAVING  includedTemplateId = 1

returns only one record with Id:2 but not record with Id:4 because JSON_SEARCH with 'all' delivers an array of paths, but JSON_EXTRACT does not allow path to be an array.

What is not possible

I also tried using a simple LIKE expression, but ended on the problem, if the order or the objects argument differ (p.e.: {id: number, type: "template"}) or a space or different quotes are used the like does not match.

Additional goal

It would be the most perfekt result, if i get record #5 too for a search after template-id #1, because #5 uses #2, which uses #1. But this would be next level.

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

Answer 2

Solution for MySql 5.7 (and I think mariaDb too)

select tp.id,tp.TemplateData
from template tp
where json_contains( tp.TemplateData ,json_object('type','template','id',1))
;