angularjs - How to send nested json from psql and use it to create nested list?

Question

So, I'm using following query to send the nested JSON to the frontend. I'm using angular-nestable in the front which used item from the JSON and builds the nested list. The frontend is working fine, but the json is not creating single parent, the list seems broken when child is added to the second chid, as you can see from the picture.

the query I'm using is as follows: the db is a list of subcategory which has title, id, categoryId and subcategoryId (which is the parent)

CREATE SEQUENCE IF NOT EXISTS cbpcategorydriver_id_seq; $1 indicated the categoryId

-- Table Definition
CREATE TABLE "public"."cbpcategorydriver" (
    "title" text,
    "id" int4 NOT NULL DEFAULT nextval('cbpcategorydriver_id_seq'::regclass),
    "customerbusinessplanid" int4,
    "createdbyid" text,
    "createdat" timestamptz,
    "updatedat" timestamptz,
    "categorydriverid" int4,
    "description" text,
    PRIMARY KEY ("id")
);    

WITH RECURSIVE subcategory_with_level AS (
                SELECT *,
                       0 AS lvl,
                        jsonb_build_object('title', title, 'id', id) as item

              FROM subcategory
             WHERE subcategoryid IS NULL and categoryid = $1 and deletedAt is null
          
            UNION ALL
          
            SELECT child.*,
                   parent.lvl + 1,
                   jsonb_build_object('title', child.title, 'id', child.id) as item
              FROM subcategory child 
              JOIN subcategory_with_level parent ON parent.id = child.subcategoryid 
              WHERE child.categoryid = $1 and child.deletedAt is null
          ),
          maxlvl AS (
            SELECT max(lvl) maxlvl FROM subcategory_with_level
          ),
          c_tree AS (
            SELECT subcategory_with_level.*,
                   NULL::JSONB children
              FROM subcategory_with_level, maxlvl
             WHERE lvl = maxlvl
          
             UNION
          
             (
               SELECT (branch_parent).*,
                      jsonb_agg(branch_child)
                 FROM (
                   SELECT branch_parent,
                          to_jsonb(branch_child) - 'lvl' - 'subcategoryid' - 'id' AS branch_child
                     FROM subcategory_with_level branch_parent
                     JOIN c_tree branch_child ON branch_child.subcategoryid = branch_parent.id
                 ) branch
                 GROUP BY branch.branch_parent
          
                 UNION
          
                 SELECT c.*,
                        NULL::JSONB
                 FROM subcategory_with_level c
                 WHERE NOT EXISTS (SELECT 1
                                     FROM subcategory_with_level hypothetical_child
                                    WHERE hypothetical_child.subcategoryid = c.id)
             )
          )
          
          SELECT jsonb_pretty(
                   array_to_json(
                     array_agg(
                       row_to_json(c_tree)::JSONB - 'lvl' - 'subcategoryid' - 'id'
                     )
                   )::JSONB
                 ) AS tree
            FROM c_tree
            WHERE lvl=0;

In the picture 1111 subcategory is listed as 2 parents, when it should have been one.

Answer 1

等待大神答复