考虑下表
Device
--------
id
name
type
--------
components
--------
id INT
type VARCHAR
--------
Manufacturers
-------------
id INT
name VARCHAR
country VARCHAR
-------------
Device_components
-----------------
deviceid REFERENCES Devices(id)
componentid REFERENCES Components(id)
-----------------
Component_Manufacturers
-----------------------
componentid REFERENCES Components(id)
manufacturerid REFERENCES Manufacturers(id)
-----------------------
我想查询数据库以返回如下内容：
{
"id": 1,
"name": "phone",
"components": [
{
"id": 1,
"type": "screen",
"manufacturers": [
{
"id": 1,
"name": "a",
"country": "Germany"
}
]
},
{
"id": 2,
"type": "keyboard",
"manufacturers": [
{
"id": 1,
"name": "a",
"country": "UK"
}
]
}
]
}
到目前为止，我分别从每个表中进行选择，然后在我的应用程序中组装 JSON 对象。
以下是一些示例数据：
CREATE TABLE IF NOT EXISTS Devices
(
ID SERIAL PRIMARY KEY NOT NULL,
NAME VARCHAR(30) UNIQUE NOT NULL
);
CREATE TABLE IF NOT EXISTS Components
(
ID SERIAL PRIMARY KEY NOT NULL,
NAME VARCHAR(30) UNIQUE NOT NULL
);
CREATE TABLE IF NOT EXISTS Manufacturers
(
ID SERIAL PRIMARY KEY NOT NULL,
NAME VARCHAR(30) UNIQUE NOT NULL,
COUNTRY VARCHAR(40)
);
CREATE TABLE IF NOT EXISTS Device_components
(
DeviceID INT REFERENCES Devices(ID),
ComponentID INT REFERENCES Components(ID)
);
CREATE TABLE IF NOT EXISTS Component_manufacturers
(
ComponentID INT REFERENCES Components(ID),
ManufacturerID INT REFERENCES Manufacturers(ID)
);
INSERT INTO Devices (NAME) VALUES ('phone');
INSERT INTO Devices (NAME) VALUES ('tablet');
INSERT INTO Devices (NAME) VALUES ('pc');
INSERT INTO Components (NAME) VALUES ('mouse');
INSERT INTO Components (NAME) VALUES ('camera');
INSERT INTO Components (NAME) VALUES ('screen');
INSERT INTO Manufacturers (NAME,Country) VALUES ('foo','france');
INSERT INTO Manufacturers (NAME,Country) VALUES ('bar','spain');
INSERT INTO Manufacturers (NAME,Country) VALUES ('baz','germany');
INSERT INTO Device_components VALUES (1,2);
INSERT INTO Device_components VALUES (1,3);
INSERT INTO Device_components VALUES (2,2);
INSERT INTO Device_components VALUES (2,3);
INSERT INTO Device_components VALUES (3,1);
INSERT INTO Device_components VALUES (3,2);
INSERT INTO Device_components VALUES (3,3);
INSERT INTO Component_manufacturers VALUES (1,1);
INSERT INTO Component_manufacturers VALUES (1,2);
INSERT INTO Component_manufacturers VALUES (1,3);
INSERT INTO Component_manufacturers VALUES (2,2);
INSERT INTO Component_manufacturers VALUES (3,3);
好的，所以您似乎想从连接树创建一个图。这是一件很自然的事情。
这并不像在 Pg 中那么容易，主要是因为
record伪类型中缺乏对列别名的支持。这是我想出的：
这里的一般想法是，在生成对象数组的每个对象嵌套级别，
json_agg使用group_by. 该json_agg函数隐式调用row_to_json将行类型转换为 json。在SELECT子句中为合成列指定别名，以便在将行馈送到外部级别时 json 键名是正确的。由于未聚合外部级别，因此请使用
row_to_json子查询而不是使用json_agg. 如果您想要单个 json 结果而不是一组 json 行，您可以更改row_to_json为json_agg在外层。我只在 9.3 上测试过，因为这是我安装的，而且 sqlfiddle 似乎有一些问题。
更新：不幸的是
json_agg在 9.2 上不存在，它是在 9.3 中添加的。但是，这是一个足够简单的情况，您可以使用它array_agg，所以这应该适用于 9.2：理想情况下，PostgreSQL 会更自然地支持这种查询，没有子查询，但我认为我们需要一种新的连接类型，或者至少需要一些函数来与横向查询一起使用。无论如何，这一切都计划成一个文明的查询计划（尽管使用合适的索引会更好）：