类别和子类别递归查询SQL

Question

我的递归查询出了一点小问题。我有一个酒吧菜单的数据库。我们得到:类别，每个类别都有子类别，每个子类别都有多个项目。

数据库是这样的，查询链接在里面：

CREATE TABLE category (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  title varchar(255) NOT NULL,
  parent_id int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (parent_id) REFERENCES category (id) 
    ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE `items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `cat_id` int unsigned DEFAULT NULL,
  `parent_id` int unsigned DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `cat_id` (`cat_id`),
  KEY `sub_id` (`parent_id`),
  CONSTRAINT `cat_id` FOREIGN KEY (`cat_id`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `sub_id` FOREIGN KEY (`parent_id`) REFERENCES `category` (`parent_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

BEGIN;
INSERT INTO `category` VALUES (1, 'Colazione', NULL);
INSERT INTO `category` VALUES (2, 'Pranzo', NULL);
INSERT INTO `category` VALUES (3, 'Primi piatti', 2);
INSERT INTO `category` VALUES (4, 'Second dish', 2);
INSERT INTO `category` VALUES (5, 'Other things for lunch', 2);
COMMIT;

-- ----------------------------
-- Records of items
-- ----------------------------
BEGIN;
INSERT INTO `items` VALUES (1, 1, NULL, 'Cornetto');
INSERT INTO `items` VALUES (2, 3, 2, 'Pasta al sugo 1');
INSERT INTO `items` VALUES (3, 3, 2, 'Pasta al sugo 2');
INSERT INTO `items` VALUES (4, 3, 2, 'Pasta al sugo 3');
INSERT INTO `items` VALUES (5, 3, 2, 'Pasta al sugo 1 X');
INSERT INTO `items` VALUES (6, 3, 2, 'Pasta al sugo 2 X');
INSERT INTO `items` VALUES (7, 4, 2, 'Pasta al sugo 3 X');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

查询：

with combine_trees as (
with make_tree as (
WITH RECURSIVE category_path  AS
(
  SELECT id, title, parent_id
    FROM category
    WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.title, c.parent_id
    FROM category_path AS cp JOIN category AS c
      ON cp.id = c.parent_id
)
SELECT cp.title, cp.id,
       if(cp.id = category.id,
          json_arrayagg(json_object('item_name', it.name)),
          json_object(cp.title, json_object('items',json_arrayagg(json_array(json_object('item_name', it.name))))))
        as tree
FROM category_path cp
INNER JOIN items it ON it.cat_id = cp.id
join category on category.id =  ifnull(cp.parent_id, cp.id)
group by cp.title, cp.id, category.id
)
select json_arrayagg(json_object(title, json_array('items', tree))) output_json from make_tree group by id
)
select json_object('menu',group_concat(output_json)) as output from combine_trees;

https://sqlize.online/

问题是它不是以JSON格式打印结果，而是以单字符串的格式打印结果。如果不让所有输出都是唯一的字符串，我们如何将其转换为JSON？

Answer 1

在你最后一句话里，

select json_object('menu',group_concat(output_json)) as output from combine_trees

您不能使用group_concat来组合从第2行到最后一行获得的json数组(例如select json_arrayagg(...) output_json from make_tree group by id)。

您得到的每个数组看起来都像[...]，而group_concat将为您提供[...], [...]。这不是一个有效的json数组(需要用括号括起来，例如[[...],[...]])，而是一个字符串，从它创建一个json对象会将该字符串作为值。

要组合您的json数组，您可以(像以前一样)使用json_arrayagg而不是group_concat，例如

select json_object('menu',json_arrayagg(output_json)) as output from combine_trees