使用JSON_BUILD_OBJ从Postgres返回JSON

5vf7fwbs 于 2023-03-24 发布在其他

关注(0)|答案(3)|浏览(125)

我有一个Postgres DB设置，我试图从查询中返回一个特定的数据形状。
我的数据如下所示：

CREATE TABLE resource_actions(
resource TEXT NOT NULL,
actions TEXT NOT NULL,
entity TEXT NOT NULL,
UNIQUE(resource, actions, entity)
);

INSERT INTO resource_actions (resource, actions, entity) VALUES
('reading-list', 'read', 'Citizen'),
('reading-list', 'read', 'Employee'),
('books', 'read', 'Employee'),
('titles', 'update', 'Citizen'),
('titles', 'read', 'Citizen'),
('titles', 'update', 'Employee'),
('titles', 'read', 'Employee'),
('authors', 'update', 'Employee'),
('authors', 'read', 'Citizen'),
('reviews', 'read', 'Citizen'),
('reviews', 'read', 'Employee'),
('reviews', 'create', 'Citizen'),
('reviews', 'create', 'Employee'),
('employees', 'read', 'Employee'),
('employees', 'read', 'Boss'),
('employees', 'delete', 'Boss'),
('employees', 'create', 'Boss'),
('employees', 'update', 'Boss'),
('employee-schedule', 'read', 'Boss'),
('employee-schedule', 'delete', 'Boss'),
('employee-schedule', 'create', 'Boss'),
('employee-schedule', 'update', 'Boss');

我想像这样返回数据：

{
    read:['Citizen':['reading-list', 'titles', 'authors','reviews'], 'Employee':['books','reading-list', 'titles', 'authors'], 'Boss':[]]
    create:['Citizen':['reviews'], 'Employee':['reviews'], 'Boss':['employees','employee-schedule']],
    update: ['Citizen':['titles'], 'Employee':['authors','titles'], 'Boss':['employees','employee-schedule']],
    delete:['Citizen':[], 'Employee':[], 'Boss':['employees','employee-schedule']]
}

我有一个接近的SQL查询，我只是不能为我的生活弄清楚我需要改变什么来获得正确的数据形状。我的查询是在这里：

select actions, 
JSON_AGG(JSON_BUILD_OBJECT(resource, entity)) AS groupedActions
FROM resource_actions 
GROUP BY actions

我有一个SQL小提琴显示我到目前为止所拥有的：http://sqlfiddle.com/#!17/93bf5/1/0
它让我接近了，但我不明白如何通过entity聚合数据并显示他们可以访问的资源的数组。

JSON

来源：https://stackoverflow.com/questions/75814668/return-json-from-postgres-with-json-build-obj

3条答案

按热度按时间

5anewei61#

select json_build_object(actions, array_agg(groupedResources)) as jsonResult
from (select actions, json_build_object(entity, array_agg(resource)) as groupedResources
      from resource_actions
      group by actions, entity) tmp
group by actions;

| json结果|
| - ------|
| {“create”：[{“公民”：[“reviews”]}，{“Employee”：[“reviews”]}，{“Boss”：[“employees”，“employee-schedule”]}]}|
| {“read”：[{“Boss”：[“employees”，“employee-schedule”]}，{“Citizen”：[“reading-list”，“titles”，“authors”，“reviews”]}，{“Employee”：[“阅读列表”，“书籍”，“标题”，“评论”，“员工”]}]}|
| {“update”：[{“Boss”：[“employees”，“employee-schedule”]}，{“Citizen”：[“titles”]}，{“Employee”：[“标题”、“作者”]}]|
| {“delete”：[{“Boss”：[“employees”，“employee-schedule”]}]}|
DBfiddle demo
全部为单个JSON：

select json_agg(jsonResult)
from (
select json_build_object(actions, json_agg(groupedResources)) as jsonResult
from (select actions, json_build_object(entity, array_agg(resource)) as groupedResources
      from resource_actions
      group by actions, entity) tmp
group by actions) x;

DBFiddle demo

赞(0）回复(0）举报 2023-03-24

lawou6xi2#

若要聚合具有串联的操作，可以使用自定义聚合

CREATE AGGREGATE json_concat_agg(jsonb) (
  SFUNC = 'jsonb_concat',
  STYPE = jsonb,
  INITCOND = '{}'
);

select json_concat_agg(ga::jsonb)::json
    from (
    select JSON_BUILD_OBJECT(actions, JSON_AGG(ge)) ga
    from (
       select actions, JSON_BUILD_OBJECT(entity, JSON_AGG(resource)) AS ge
       FROM resource_actions 
       GROUP BY actions, entity
    ) t
    group by actions
) t

db<>fiddle

{
"read": [{"Boss": ["employees", "employee-schedule"]}, {"Citizen": ["reading-list", "titles", "authors", "reviews"]}, {"Employee": ["reading-list", "books", "titles", "reviews", "employees"]}], 
"create": [{"Citizen": ["reviews"]}, {"Employee": ["reviews"]}, {"Boss": ["employees", "employee-schedule"]}],
"delete": [{"Boss": ["employees", "employee-schedule"]}], 
"update": [{"Boss": ["employees", "employee-schedule"]}, {"Citizen": ["titles"]}, {"Employee": ["titles", "authors"]}]
}

赞(0）回复(0）举报 2023-03-24

lh80um4z3#

您可以在子查询/CTE s中构造对象，然后使用jsonb_object_agg()收集它们：demo

select jsonb_object_agg(actions,entity_resources) result 
from (select actions,
             jsonb_agg(entity_resources) entity_resources
      from (select actions,
                   jsonb_build_object(entity,jsonb_agg(resource)) entity_resources
            from resource_actions 
            group by actions,
                     entity ) innermost_subquery
      group by actions ) subquery;

如果可以，请使用jsonb：它是可索引的，更轻，更快，更灵活，提供更多的函数和操作符，包括jsonpath交互，在输入上的处理成本很小。如果你不能，上面所有的jsonb函数都有直接的json等价物，它们可以被交换出来。
除了下面的jsonb_pretty()，在这里你必须将json转换为jsonb。如果你想美化一些基于文本的json，你可以将它作为字符串文本输入-它将被检测为unknown并自动转换为支持的类型。
通过jsonb_pretty()输出：

{
    "read": [
        {
            "Boss": [
                "employees",
                "employee-schedule"
            ]
        },
        {
            "Citizen": [
                "reading-list",
                "titles",
                "authors",
                "reviews"
            ]
        },
        {
            "Employee": [
                "reading-list",
                "books",
                "titles",
                "reviews",
                "employees"
            ]
        }
    ],
    "create": [
        {
            "Citizen": [
                "reviews"
            ]
        },
        {
            "Employee": [
                "reviews"
            ]
        },
        {
            "Boss": [
                "employees",
                "employee-schedule"
            ]
        }
    ],
    "delete": [
        {
            "Boss": [
                "employees",
                "employee-schedule"
            ]
        }
    ],
    "update": [
        {
            "Boss": [
                "employees",
                "employee-schedule"
            ]
        },
        {
            "Citizen": [
                "titles"
            ]
        },
        {
            "Employee": [
                "titles",
                "authors"
            ]
        }
    ]
}

赞(0）回复(0）举报 2023-03-24

我来回答

使用JSON_BUILD_OBJ从Postgres返回JSON

3条答案

相关问题

热门标签

最新问答