Как между запросами вызвать цикл?

Question

[Andrey Grechikhin]

MYSQL 8, Поставлена задача, в которой свойства продукта в виде вложенного JSON объекта вносится в ячейку итоговой таблицы.

SELECT JSON_OBJECT('id', id, 'title', title) AS category, property_category.parent_id INTO 
@obj, @pid  FROM property_category WHERE id = 4;

WHILE @pid > 0 
BEGIN
	SELECT JSON_OBJECT('id', id, 'title', title, 'category', @obj) AS category, 
property_category.parent_id INTO @obj, @pid FROM property_category 
	WHERE property_category.id = @pid
END;

SELECT *, @obj AS category, @pid AS parent_id FROM product

Comments

[Andrey Grechikhin]

Изначально не предполагалось у категории более 3 уровней вложенности, сейчас их может быть от 1 и до хз скольки, потому прибегаю к циклу, рекурсия не подходит, т.к. она плодит множество записей, которые в итоге вновь придется объединять

Answer 1

[Rsa97]

Тут не нужны никакие циклы.
GROUP BY, JSON_ARRAYAGG() и LEFT JOIN.

Comments

[Andrey Grechikhin]

К сожалению этот вариант не подойдет, в примере малая часть итогового запроса
Одна из ячеек будет иметь вид: {id: n, title:t, category: {id: n, title:t, category {...}} и т. д. При этом вложенность может быть любого уровня

[Rsa97]

Andrey Grechikhin, И что? Рекурсивные CTE ещё никто не отменял.

[Andrey Grechikhin]

Rsa97, И как мне сделть вложение более 2 уровней, если присваивание внутри запроса в виде SELECT .... var = JSON_OBJECT(...), а INTO запрещается внутри рекурсивного метода?

[Rsa97]

Andrey Grechikhin,
https://www.db-fiddle.com/f/6v4JZAbH6r1Fnk1tzxhnB1/0

spoiler

CREATE TABLE `test` (
  `id` INT,
  `title` VARCHAR(32),
  `parent_id` INT
);
    
INSERT INTO `test` VALUES
  (1, 'Category 1', NULL),
  (2, 'SubCategory 1.1', 1),
  (3, 'SubCategory 1.2', 1),
  (4, 'SubSubCategory 1.1.1', 2),
  (5, 'Category 2', NULL);

WITH RECURSIVE `cte` AS (
  SELECT `id`, JSON_OBJECT('id', `id`, 'title', `title`) AS `json`
    FROM `test`
    WHERE `parent_id` IS NULL
  UNION
  SELECT `t`.`id`, JSON_OBJECT('id', `t`.`id`, 'title', `t`.`title`, 'category', `c`.`json`) AS `json`
    FROM `test` AS `t`
    JOIN `cte` AS `c` ON `t`.`parent_id` = `c`.`id`
)
SELECT *
  FROM `cte`;

| id  | json                                                                                                                                        |
| --- | ------------------------------------------------------------------------------------------------------------------------------------------- |
| 1   | {"id": 1, "title": "Category 1"}                                                                                                            |
| 5   | {"id": 5, "title": "Category 2"}                                                                                                            |
| 2   | {"id": 2, "title": "SubCategory 1.1", "category": {"id": 1, "title": "Category 1"}}                                                         |
| 3   | {"id": 3, "title": "SubCategory 1.2", "category": {"id": 1, "title": "Category 1"}}                                                         |
| 4   | {"id": 4, "title": "SubSubCategory 1.1.1", "category": {"id": 2, "title": "SubCategory 1.1", "category": {"id": 1, "title": "Category 1"}}} |

[Andrey Grechikhin]

Rsa97, спасибо, действительно сработало, видимо я ранее криво писал рекурсивные запросы