How to perform a recursive query with the relations stored in a JSON array?

Question

I have a MYSQL table that relates to itself. The relations are represented in the same table through a JSON Array. Here is the table and some sample data:

CREATE TABLE `templatedata` (
  `Id` char(255) NOT NULL,
  `RelatesWith` json DEFAULT NULL
);

insert into templatedata values
('1', JSON_ARRAY('2')),
('2', JSON_ARRAY('3')),
('3', JSON_ARRAY('4')),
('4', JSON_ARRAY()),
('5', JSON_ARRAY('6', '7')),
('6', JSON_ARRAY()),
('7', JSON_ARRAY('8')),
('8', JSON_ARRAY());

What I’m trying to do is get all relations for a given id. For example id 1 relates to 2, 3, 4. While id 5 relates to 6, 7, 8. And the 7 with only 8, etc...

I managed to get the first level of relations with the following query:

With recursive cte (id, relatesWith, level) AS
(
    select id, relatesWith, 0 as level from templatedata

    union    

    select cte.id, JSON_MERGE_PRESERVE(cte.relatesWith, templatedata.relatesWith), level+1  from cte
    join templatedata on JSON_CONTAINS(JSON_ARRAY(templatedata.Id), cte.RelatesWith)
    where JSON_LENGTH(templatedata.relatesWith) <> 0 and JSON_LENGTH(cte.relatesWith) <> 0 
)
SELECT cte.* FROM cte;

But I’m having trouble getting the rest. How can I get all the relationships as I wish.

SQL fiddle

Answer 1

The query was almost right. However it is necessary to be careful to write the second query precisely, either on select, either in the join. In the select it is necessary to choose the columns so that the recursion of right. And of course in the join it is necessary to choose the appropriate condition for recursion. Here is the corrected query.

With recursive cte (id, relatesWith, level) AS
(
    select id, relatesWith, 0 as level from templatedata
    where JSON_LENGTH(templatedata.relatesWith) = 0

    union    

    select t.id, JSON_MERGE_PRESERVE(cte.relatesWith, t.relatesWith), level+1  from cte
    join templatedata t on JSON_CONTAINS(t.RelatesWith, JSON_ARRAY(cte.Id))
)
SELECT cte.* FROM cte;