Category and subcategory recursive query SQL

Question

I got a little problem with my recursive query. I got a database of menu of a bar. We got: Category, each category got sub-categories and each-subcategories got multiple items.

The database is this one and the query is linked inside:

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;

Query:

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/

The problem is that its not printing the result as JSON but its printing it formatted in one-string. How can we transform it in a JSON without that all the output is an unique string?

Try to use [`JSON_PRETTY() function`](https://dev.mysql.com/doc/refman/8.0/en/json-utility-functions.html#function_json-pretty) — Akina, Nov 06 '20 at 11:40

score 0 · Answer 1 · answered Nov 06 '20 at 14:17

0

In your last line,

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

you cannot use group_concat to combine the json arrays you get from the 2nd to last line (e.g. select json_arrayagg(...) output_json from make_tree group by id).

The arrays you get there each look like [...], and group_concat will give you [...], [...]. This is not a valid json array (which would need additional brackets around it, e.g. [[...],[...]]), but a string, and creating a json object from it will give you, well, that string as the value.

To combine your json arrays, you can use (as you did before) json_arrayagg instead of group_concat, e.g.

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

answered Nov 06 '20 at 14:17

Solarflare

10,721
2
18
35

The problem is that I am getting "items", [ inside the json instead of "items": [ The json that I got now is: "Colazione": [ "items", [ { "item_name": "Cornetto" } ] ] } ], and items should be "items": [ and actually its wrong – Michele Zotti Nov 06 '20 at 14:21
Your question was why "its printing it formatted in one-string". If you have a different problem with your json, you need to ask another question. I would assume that your problem might be `json_array('items', tree))` (which creates two entries instead of a key with the value of tree), so maybe try `json_object('items', tree))` (which, if "tree" is an array, should give you `"items": [...`). But again, for a different problem, please create a new question with expected (and current, incorrect) output (and the code directly included, as in your current question after strawberrys edit). – Solarflare Nov 06 '20 at 14:44

Category and subcategory recursive query SQL

1 Answers1