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

Question

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

Navigation

#1 by (0 votes)

0

I have a MYSQL table that relates to itself. The relationships are represented in the same table by 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 the relationships for a given id. For example, id 1 relates to 2, 3, 4 . While id 5 relates to 6, 7, 8 . And the 7 only with 8, etc ...

I was able to get the first level of relationships 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 a hard time getting the rest. How can I get all relationships as desire.

SQL fiddle

mysql json query

asked by anonymous 07.11.2018 / 11:51

1 answer

How to add external training in chatterbot How do you readjust the label of a chart js chart?

score 0 · Accepted Answer

The query was almost right. However, care must be taken to write the second query accurately, either in select or join . In select you need to choose the columns so that the recursion is right. And, of course, in% with_% it is necessary to choose the proper 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;