PostgreSQL，根据组子句构建压缩的json

Question

我正在尝试构建一个json对象，该对象在没有双倍键的情况下在JS中非常容易使用。为此，我一直在寻找一个等效的问题，但它看起来永远不像我的......

我正在做这个查询：

SELECT type_bien, nature_mutation , option
FROM layer.dvf a
LEFT JOIN (
SELECT value, option
FROM form_select
WHERE name = 'nature_mutation'
) b ON LOWER(a.nature_mutation) = LOWER(b.value)
WHERE option IS NOT NULL
GROUP BY type_bien, nature_mutation, option
order by type_bien, nature_mutation, option

这给了我这个结果：

type_bien           nature_mutation option
appartement         adjudication    Adjudication
appartement         echange         Échange
appartement         VEFA            VEFA
appartement         vente           Vente
dependance          adjudication    Adjudication
dependance          echange         Échange
dependance          VEFA            VEFA
dependance          vente           Vente
local_indus_comm    adjudication    Adjudication
local_indus_comm    echange         Échange
local_indus_comm    VEFA            VEFA
local_indus_comm    vente           Vente
maison              adjudication    Adjudication
maison              echange         Échange
maison              VEFA            VEFA
maison              vente           Vente
terrain             adjudication    Adjudication
terrain             echange         Échange
terrain             Expropriation   Expropriation
terrain             vente           Vente

以下是表layer.dvf中的一些内容：

TYPE_BIEN           NATURE_MUTATION
appartement         vente
appartement         VEFA
appartement         echange
appartement         adjudication
appartement         vente_TAB
dependance          vente
dependance          VEFA
dependance          echange
dependance          adjudication
local_indus_comm    adjudication
local_indus_comm    echange
local_indus_comm    VEFA
local_indus_comm    vente
local_indus_comm    vente_TAB
maison              vente_TAB
maison              vente
maison              VEFA
maison              echange
maison              adjudication
terrain             adjudication
terrain             echange
terrain             Expropriation
terrain             vente
terrain             vente_TAB

以下是表form_select中的一些内容：

name                value               option
nature_mutation     echange             échange
nature_mutation     expropriation       Expropriation
nature_mutation     vefa                VEFA
nature_mutation     vente               Vente
nature_mutation     adjudication        Adjudication
nature_bien         maison              Maison
nature_bien         appartement         Appartement
nature_bien         local_indus_comm    Local industriel / commercial
nature_bien         dependance          Dépendance
nature_bien         terrain             Terrain

但我没有成功建立这种结果：

{
{
"type_bien" : "appartement",
"nature_mutation" : {
{"value": "adjudication","option":"Adjudication"}, 
{"value" : "echange","option" : "Échange"}, 
{"value":"VEFA","option":"VEFA"}, 
{"value":"vente","option":"Vente"}, 
}
{
"type_bien" : "dependance",
"nature_mutation" : {
{"value":"adjudication","option":"Adjudication"}, 
{"value":"echange","option":"Échange"}, 
{"value":"VEFA","option":"VEFA"}, 
{"value":"vente","option":"Vente"}
}
}
...

但我什至不知道这是否可能...有人可以给我一种方法吗？

谢谢！

Answer 1

使用json_build_object构建一个 JSON 对象。我首先使用cte获取用于nature_mutation的 json 对象，并从group by列中为type_bien构建 json 对象

with t as(
select t1.type_bien, 
json_agg(json_build_object('value', value, 'option', option)) AS nature_mutation
from layer t1
join form_select t2
on lower(t1.nature_mutation) = lower(t2.value)
group by 1)
select json_build_object('type_bien', type_bien, 'nature_mutation', nature_mutation) 
from t;

Answer 2

所以在这里我找到了我的解决方案：

SELECT json_agg(result)
FROM (
SELECT json_build_object(
'type_bien', type_bien, 
'nature_mutation', json_agg(
json_build_object(
'value', value, 
'option', option
)
)
) as result
FROM (
SELECT type_bien, nature_mutation, value, option
FROM layer.dvf d
LEFT JOIN (
SELECT value, option
FROM form_select
) c ON LOWER(d.nature_mutation) = LOWER(c.value)
WHERE option IS NOT NULL
GROUP BY type_bien, nature_mutation, value, option
ORDER BY type_bien, nature_mutation
) b
GROUP BY type_bien
) t

第二组，允许我不重复"type_bien">