Consider the following tables
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)
-----------------------
I want to query the database to return something like this:
{
"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"
}
]
}
]
}
So far I'm selecting from each table individually and then assemble the JSON object in my application.
Here's some sample data:
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);
Best Answer
Alright, so you seem to want to create a graph from a join tree. That's a pretty natural thing to want to do.
It's not as easy as it should be in Pg, mainly because of the lack of support for column-aliases in the
record
pseudo-type.Here's what I came up with:
The general idea here is, at every level of object nesting where you're producing an array of objects, use
json_agg
in agroup_by
. Thejson_agg
function implicitly callsrow_to_json
to transform a rowtype into json. In theSELECT
clause specify aliases for the synthetic columns so the json key names are correct when the row is fed into the outer level.As the outer level isn't aggregated, use
row_to_json
over a subquery instead of usingjson_agg
. If you want a single json result instead of a set of json rows, you can changerow_to_json
tojson_agg
in the outer level.I've only tested on 9.3, as that's what I have installed and sqlfiddle seems to be having some issues.
Update: Unfortunately
json_agg
doesn't exist on 9.2, it was added in 9.3. However, this is a simple enough case that you can just usearray_agg
instead, so this should work in 9.2:Ideally PostgreSQL would support this sort of query more naturally, without the subqueries, but I think we'd need a new join type, or at least some functions to use with lateral queries. It all plans out into a civilised query plan anyway (though it'd be better with suitable indexes):