我正在嘗試從 Postgres 13.1 中的查詢生成 GeoJSON：
SELECT json_build_object(
'type', 'FeatureCollection',
'features', json_agg(
json_build_object(
'type', 'Feature',
'geometry', ST_AsGeoJSON(geom)::json,
'properties', json_build_object(
-- list of fields
'id', hardinfra.id,
-- need json_object_agg here
'protections', json_build_object(
p.ptype,
i.pscore
),
-- need json_object_agg here
'responses', json_build_object(
ep.etype,
er.response
),
'category', c.category
)
)
)
)
FROM hardinfra
JOIN expertresponse er ON hardinfra.id = er.infra_id
JOIN expert ep ON ep.id = er.expert_id
JOIN infraprotection i ON hardinfra.id = i.infra_id
JOIN protection p ON p.id = i.protection_id
JOIN category c on c.id = hardinfra.category_id
;
但是，protections并且responses應該是從多對多關系構建的鍵值集合，通常使用json_object_agg（參見連接條件）。如何解決缺少嵌套聚合呼叫來生成這些的問題？
這是 GeoJSON 輸出的樣子：
請注意，"protections"和"responses"屬性可以是任意長度，因為它們來自相關表。
{
"features": [
{
"geometry": {
"coordinates": [
-6.147880554,
53.383533145
],
"type": "Point"
},
"properties": {
"id": 90,
"protections": {
"Foo": 1,
"Bar": 2,
"Baz": 3
},
"responses": {
"A": "response A",
"B": "response B",
"C": "response C"
}
},
"type": "Feature"
}
],
"type": "FeatureCollection"
}
表格設定如下（您需要 PostGIS，或替換純文本欄位 - 這方面在這里并不重要）：
create table hardinfra (
id serial CONSTRAINT hardinfra_pkey PRIMARY KEY,
geom geometry(Geometry, 4326)
);
create table expert
(
id serial
constraint expert_pkey
primary key,
etype varchar(50)
);
create table expertresponse
(
infra_id integer not null
constraint expertresponse_infra_id_fkey
references hardinfra
on delete cascade,
expert_id integer not null
constraint expertresponse_expert_id_fkey
references expert
on delete cascade,
response varchar(280) not null,
constraint expertresponse_pkey
primary key (infra_id, expert_id)
);
create table protection
(
id serial
constraint protection_pkey
primary key,
ptype varchar(200)
);
create table infraprotection
(
infra_id integer not null
constraint infraprotection_infra_id_fkey
references hardinfra
on delete cascade,
protection_id integer not null
constraint infraprotection_protection_id_fkey
references protection
on delete cascade,
pscore integer default 0 not null,
constraint infraprotection_pkey
primary key (infra_id, protection_id)
);
uj5u.com熱心網友回復：
正如@a_horse_with_no_name 所述，您不能僅一步構建嵌套聚合，您可以嘗試這樣做，假設表 hardinfra 中每個 id 只有一個 category_id 并且表 category 中每個 id 一個類別：
SELECT json_build_object
( 'type', 'FeatureCollection'
, 'features'
, json_agg( json_build_object
( 'type', 'Feature'
, 'geometry', ST_AsGeoJSON(geom)::json
, 'properties'
, json_build_object
( 'id', hi.id
, 'protections', p.protection_list
, 'responses', r.response_list
, 'category', c.category
)
)
)
)
FROM hardinfra AS hi
INNER JOIN
( SELECT i.infra_id
, json_build_object ('protections'
, json_object_agg(p.ptype, i.pscore)
) AS protection_list
FROM infraprotection AS i
JOIN protection AS p
ON p.id = i.protection_id
) AS p
ON hi.id = p.infra_id
INNER JOIN
( SELECT er.infra_id
, json_build_object ('responses'
, json_object_agg(ep.etype, er.response)
) AS response_list
FROM expertresponse AS er
JOIN expert AS ep
ON ep.id = er.expert_id
) AS r
ON hi.id = r.infra_id
INNER JOIN category c
ON c.id = hi.category_id ;
PS：在您的 json 示例中，沒有“類別”鍵/值？
uj5u.com熱心網友回復：
這是簡單的嵌套選擇，正如我最初所說，您不需要內部連接：
SELECT json_build_object(
'type', 'FeatureCollection',
'features', json_agg(
json_build_object(
'type', 'Feature',
'geometry', ST_AsGeoJSON(geom)::json,
'properties', (
select json_build_object(
'id', hardinfra.id,
'responses',
(
select json_object_agg(e.etype, er.response)
from expert e
inner join expertresponse er on e.id = er.expert_id
where infra_id = hardinfra.id
),
'protections',
(
select json_object_agg(p.ptype, i.pscore)
from protection p
inner join infraprotection i on p.id = i.protection_id
where infra_id = hardinfra.id
)
))
)
))
from hardinfra;
這是DBFiddle 演示。
注意：在 DBFiddle 中，我無法創建擴展 postgis，所以我評論了幾何型別，而是用文本模擬（使用幾何型別，它會輸出如下：
"geometry": {
"type": "Point",
"coordinates": [
-6.147880554,
53.383533145
]
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/343262.html
標籤：sql json PostgreSQL
上一篇：修復SQL觸發器語法