从Postgres获取有序JSON树

b09cbbtk  于 2022-12-30  发布在  其他
关注(0)|答案(1)|浏览(107)

我正在开发一个动态菜单，我需要获得一个JSON树，其中包含数据，以便根据三个表中定义的约束（SQL Fiddle仍然有postgres 9.6，但我使用的是postgres 15.0）在界面中构建选项（使用CSS、HTML和JS）
一般的想法是首先找到指向文件的节点（文件不为空），这些文件处于活动状态（状态= A）且来自子系统1（子系统ID = 1），只要用户1（id_user = 1）也处于活动状态（status = A）并且具有访问权限（根据*users_modules*表）
对于这些记录，只要找到递归相关的每个父记录并将它们添加到树中就足够了，但要保持group_order字段定义的顺序

用户

CREATE TABLE users (
  id SMALLINT NOT NULL,
  name CHARACTER VARYING(20) NOT NULL,
  status CHAR(1) NOT NULL
);

| 身份证|姓名|地位|
| - ------| - ------| - ------|
| 1个|无名氏|A类|
| 第二章|无名氏|A类|
| 三个|杜杜|我|

模块

CREATE TABLE modules (
  id SMALLINT NOT NULL,
  id_subsystem SMALLINT NOT NULL,
  id_master SMALLINT,
  group_order SMALLINT NOT NULL,
  label CHARACTER VARYING(30) NOT NULL,
  file CHARACTER VARYING(30),
  icon CHARACTER VARYING(20),
  status CHAR(1) NOT NULL
);
INSERT INTO
  modules
VALUES
  (1,1,NULL,1,'Dashboard','dashboard','dashboard','A'),
  (2,1,NULL,2,'Registrations',NULL,'cabinet','A'),
  (3,1,2,1,'Customers','customers',NULL,'A'),
  (4,1,2,2,'Suppliers','suppliers',NULL,'A'),
  (5,1,2,3,'Products','products',NULL,'A'),
  (6,1,2,4,'Staff',NULL,NULL,'A'),
  (7,1,6,1,'Countries','countries',NULL,'A'),
  (8,1,6,2,'States','states',NULL,'A'),
  (9,1,6,3,'Cities','cities',NULL,'A'),
  (10,1,6,4,'Means of contacts',NULL,NULL,'A'),
  (11,1,10,1,'Electronic contacts','electronic_contacts',NULL,'A'),
  (12,1,10,2,'Phone contacts','phone_contacts',NULL,'A'),
  (13,1,10,3,'Deprecated contacts','deprecated_contacts',NULL,'I'),
  (14,1,NULL,3,'Settings','settings','sliders','A'),
  (15,2,NULL,1,'Dashboard','dashboard','dashboard','A'),
  (16,2,NULL,2,'Financial',NULL,'cash','A'),
  (17,2,16,1,'Bills to pay','bills_to_pay',NULL,'A'),
  (18,2,16,2,'Bills to receive','bills_to_receive',NULL,'A');

| 身份证|id_子系统|id_主服务器|订单|标号|锉|图标|地位|
| - ------| - ------| - ------| - ------| - ------| - ------| - ------| - ------|
| 1个|1个|零|1个| Jmeter 板| Jmeter 板| Jmeter 板|A类|
| 第二章|1个|零|第二章|登记|零|内阁|A类|
| 三个|1个|第二章|1个|客户|客户|零|A类|
| 四个|1个|第二章|第二章|供应商|供应商|零|A类|
| 五个|1个|第二章|三个|产品名称|产品|零|A类|
| 六个|1个|第二章|四个|工作人员|零|零|A类|
| 七|1个|六个|1个|国家|国家|零|A类|
| 八个|1个|六个|第二章|国家|国家|零|A类|
| 九|1个|六个|三个|城市|城市|零|A类|
| 十个|1个|六个|四个|联系方式|零|零|A类|
| 十一|1个|十个|1个|电子触点|电子触点|零|A类|
| 十二|1个|十个|第二章|电话联系人|电话_联系人|零|A类|
| 十三|1个|十个|三个|弃用的联系人|弃用联系人|零|我|
| 十四|1个|零|三个|设置|设置|滑块|A类|
| 十五|第二章|零|1个| Jmeter 板| Jmeter 板| Jmeter 板|A类|
| 十六|第二章|零|第二章|财务|零|现金|A类|
| 十七|第二章|十六|1个|待付账单|待付账单|零|A类|
| 十八|第二章|十六|第二章|待收票据|待收票据|零|A类|

用户_模块

CREATE TABLE users_modules (
  id_user SMALLINT NOT NULL,
  id_module SMALLINT NOT NULL
);
INSERT INTO
  users_modules
VALUES
  (1,1),
  (1,3),
  (1,4),
  (1,5),
  (1,7),
  (1,8),
  (1,11),
  (1,12);

| id_用户|id_模块|
| - ------| - ------|
| 1个|1个|
| 1个|三个|
| 1个|四个|
| 1个|五个|
| 1个|七|
| 1个|八个|
| 1个|十一|
| 1个|十二|
我创建了下面的查询，它看起来非常接近于解决问题，但我仍然不知道它是什么

WITH RECURSIVE
  sub1 (id_master,sub) AS
  (
    (

/*

  THE FIRST PART OF A RECURSIVE CTE IS FOR NON-RECURSIVE DATA

  HERE I GET ALL THE RECORDS THAT POINT TO A FILE THAT CAN BE ACCESSED BY THE USER, BUT ONLY IF IT DOESN'T HAVE THE ROOT AS THE PARENT

*/
      SELECT
        B.id_master,
        JSONB_AGG(
          JSONB_BUILD_OBJECT(
            'icon',B.icon,
            'label',B.label,
            'module',B.file
          )
          ORDER BY
            B.group_order
        ) AS sub
      FROM
        (
          SELECT
            X.id_module
          FROM
            users_modules X
          INNER JOIN
            users Y
          ON
            X.id_user=Y.id
          WHERE
            X.id_user=1 AND
            Y.status='A'
        ) A
      INNER JOIN
        modules B
      ON
        A.id_module=B.id
      WHERE
        B.id_master IS NOT NULL AND
        B.id_subsystem=1 AND
        B.status='A'
      GROUP BY
        B.id_master

    )
    UNION ALL
    (

/*

  THE SECOND PART OF A RECURSIVE CTE IS FOR RECURSIVE DATA

  HERE I ASSEMBLE THE TREE CONNECTING ALL FINAL NODES AND BRANCHES POINTED RECURSIVELY

*/
      SELECT
        A.id_master,
        JSONB_BUILD_OBJECT(
          'icon',A.icon,
          'label',A.label,
          'sub',B.sub
        ) AS sub
      FROM
        modules A
      INNER JOIN
        sub1 B
      ON
        A.id=B.id_master
      WHERE
        A.status='A'
      ORDER BY
        A.group_order

    )
  )

SELECT
  JSONB_AGG(sub ORDER BY group_order)
FROM
  (
    SELECT
      sub,
      group_order
    FROM
      (
        (

/*

  TYING AT THE ROOT ALL KNOTS POINTING TO THE ROOT

*/
          SELECT
            JSONB_BUILD_OBJECT(
              'icon',A.icon,
              'label',A.label,
              'sub',B.sub
            ) AS sub,
            A.group_order
          FROM
            modules A
          INNER JOIN
            sub1 B
          ON
            A.id=B.id_master
          WHERE
            A.id_master IS NULL AND
            A.id_subsystem=1 AND
            A.status='A'

        )
        UNION ALL
        (

/*

  ADDING ALL USER ACCESSIBLE FILE NODES THAT HAVE ROOT AS THE PARENT

*/
          SELECT
            JSONB_BUILD_OBJECT(
              'icon',B.icon,
              'label',B.label,
              'module',B.file
            ) AS sub,
            B.group_order
          FROM
            (
              SELECT
                A.id_module
              FROM
                users_modules A
              INNER JOIN
                modules B
              ON
                A.id_module=B.id
              WHERE
                A.id_user=1 AND
                B.id_master IS NULL AND
                B.status='A'
              GROUP BY
                A.id_module
            ) A
          INNER JOIN
            modules B
          ON
            A.id_module=B.id AND
            B.status='A'

        )
      ) sub2
  ) sub3

我得到的结果是：

[
  {
    "icon": "dashboard",
    "label": "Dashboard",
    "module": "dashboard"
  },
  {
    "icon": "cabinet",
    "label": "Registrations",
    "sub":
      [
        {
          "icon": null,
          "label": "Customers",
          "module": "customers"
        },
        {
          "icon": null,
          "label": "Suppliers",
          "module": "suppliers"
        },
        {
          "icon": null,
          "label": "Products",
          "module": "products"
        }
      ]
  },
  {
    "icon": "cabinet",
    "label": "Registrations",
    "sub":
      {
        "icon": null,
        "label": "Staff",
        "sub":
          [
            {
              "icon": null,
              "label": "Countries",
              "module": "countries"
            },
              {
              "icon": null,
              "label": "States",
              "module": "states"
            }
          ]
      }
  },
  {
    "icon": "cabinet",
    "label": "Registrations",
    "sub":
      {
        "icon": null,
        "label": "Staff",
        "sub":
          {
            "icon": null,
            "label": "Means of contacts",
            "sub":
              [
                {
                  "icon": null,
                  "label": "Electronic contacts",
                  "module": "electronic_contacts"
                },
                {
                  "icon": null,
                  "label": "Phone contacts",
                  "module": "phone_contacts"
                }
              ]
          }
      }
  }
]

但我需要的是：

[
  {
    "icon": "dashboard",
    "label": "Dashboard",
    "module": "dashboard"
  },
  {
    "icon": "cabinet",
    "label": "Registrations",
    "sub":
      [
        {
          "icon": null,
          "label": "Customers",
          "module": "customers"
        },
        {
          "icon": null,
          "label": "Suppliers",
          "module": "suppliers"
        },
        {
          "icon": null,
          "label": "Products",
          "module": "products"
        },
        {
          "icon": null,
          "label": "Staff",
          "sub":
            [
              {
                "icon": null,
                "label": "Countries",
                "module": "countries"
              },
                {
                "icon": null,
                "label": "States",
                "module": "states"
              },
              {
                "icon": null,
                "label": "Means of contacts",
                "sub":
                  [
                    {
                      "icon": null,
                      "label": "Electronic contacts",
                      "module": "electronic_contacts"
                    },
                    {
                      "icon": null,
                      "label": "Phone contacts",
                      "module": "phone_contacts"
                    }
                  ]
              }
            ]
        }
      ]
  },
  {
    "icon": "sliders",
    "label": "Settings",
    "module": "settings"
  }
]

***registrations***节点的部分成员未嵌套在其中，并且***settings***节点已丢失

快到了...

JSON

1条答案

按热度按时间
anhgbhbe

anhgbhbe1#

以下查询根据您的数据集提供预期结果：

WITH RECURSIVE list AS (
SELECT m.id_subsystem
     , p.id :: integer AS id_master
     , array_agg(m.id :: integer ORDER BY m.group_order) AS children_array
     , bool_and(NOT EXISTS(SELECT 1 FROM modules where id_master = m.id)) AS leaves_only
     , jsonb_build_object('icon', p.icon, 'label', p.label,'sub', jsonb_agg(jsonb_build_object('icon', m.icon, 'label', m.label, 'module', m.file) ORDER BY m.group_order)) AS module_hierarchy
  FROM modules m
  LEFT JOIN modules p
    ON p.id = m.id_master
 GROUP BY m.id_subsystem, p.id, p.icon, p.label
), tree AS (
SELECT id_subsystem
     , id_master
     , module_hierarchy
  FROM list
 WHERE leaves_only -- starts with the leaf modules
UNION ALL
SELECT t.id_subsystem
     , l.id_master
     , jsonb_set(l.module_hierarchy, array['sub', (array_position(l.children_array, t.id_master) - 1) :: text], t.module_hierarchy, true)
  FROM tree t
 INNER JOIN list l
    ON l.children_array @> array[t.id_master] -- bottom-up tree build
   AND l.id_subsystem = t.id_subsystem
 WHERE t.id_master IS NOT NULL
)
SELECT id_subsystem, jsonb_pretty(module_hierarchy->'sub')
  FROM tree
 WHERE id_master IS NULL

部分条件尚未落实：模块和用户的状态，用户_模块关系，但是添加这些条件应该不是什么大问题。
参见dbfiddle中的测试结果

赞(0）分享  回复(0）举报  2022-12-30
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com