如何在PostgreSQL中获取返回JSON格式的JOIN查询结果？

测试环境准备 #

先创建测试表并插入数据：

CREATE TABLE backend.product (
id          Integer     NOT NULL,
category        text        NOT NULL,
title           text        NOT NULL,
price           money       NOT NULL
);

CREATE TABLE backend.product_details (
id          Integer     NOT NULL,
type            text        NOT NULL,
description     text        NOT NULL
);

CREATE TABLE backend.shipping (
id          Integer     NOT NULL,
description     text        NOT NULL,
price           money       NOT NULL
);

INSERT INTO backend.product (id, category, title, price) VALUES (1, 'sweatshirts', 'hoodie', '$50.00');
INSERT INTO backend.product_details (id, type, description) VALUES (1, 'color', 'red');
INSERT INTO backend.product_details (id, type, description) VALUES (1, 'color', 'blue');
INSERT INTO backend.product_details (id, type, description) VALUES (1, 'color', 'green');
INSERT INTO backend.product_details (id, type, description) VALUES (1, 'size', 'small');
INSERT INTO backend.product_details (id, type, description) VALUES (1, 'size', 'large');
INSERT INTO backend.shipping (id, description, price) VALUES (1, 'standard box', '$17.05');

原查询问题 #

原查询返回多行扁平化数据，无法满足嵌套JSON的需求：

SELECT p.id, p.category, p.title, p.price, s.description as shipping_box, s.price as shipping_cost, pd.type, pd.description AS choice
FROM backend.product p, backend.shipping s, backend.product_details pd
WHERE p.id = s.id
AND p.id = 1
AND p.id IN (
    SELECT pd.id
    FROM backend.product_details pd
    WHERE pd.id = 1
    GROUP BY pd.id, pd.type, pd.description
);

期望的JSON结构 #

[
    {
      "id": 1,
      "category": "sweatshirts",
      "title": "hoodie",
      "price": "$50.00",
      "shipping": { 
          "box": "standard box",
          "cost": "$17.05"
        },
      "choices": [ {
          "color": [ "red", "blue", "green" ],
          "size": [ "small", "large" ]
        }  ]
    }
]

解决方案SQL #

使用PostgreSQL的JSON聚合函数直接生成目标结构：

SELECT json_agg(
    json_build_object(
        'id', p.id,
        'category', p.category,
        'title', p.title,
        'price', p.price::text,
        'shipping', json_build_object(
            'box', s.description,
            'cost', s.price::text
        ),
        'choices', array_agg(
            json_object_agg(pd.type, pd.choices)
        )
    )
) AS result
FROM backend.product p
JOIN backend.shipping s ON p.id = s.id
JOIN (
    SELECT 
        id,
        type,
        array_agg(description) AS choices
    FROM backend.product_details
    WHERE id = 1
    GROUP BY id, type
) pd ON p.id = pd.id
WHERE p.id = 1
GROUP BY p.id, p.category, p.title, p.price, s.description, s.price;

关键逻辑说明 #

• json_agg()：将单行聚合结果打包为最外层的JSON数组
• json_build_object()：构建嵌套JSON对象，对应product基础字段和shipping子结构
• 子查询中的array_agg()：把同一类型下的选项合并为数组（比如将red/blue/green合并为color数组）
• json_object_agg()：将类型和对应选项数组转换为键值对，组成choices内的结构
• ::text：将money类型转为文本，避免JSON中出现额外类型标识

内容的提问来源于stack exchange，提问作者Pax