如何从同一个表中选择多个值作为单个值(PostgreSQL)

Question

我有一个名为available_service_information的表(这只是其中的一部分(：

| service_provider | correlation_id | service_transaction_code | did_service_transaction_code | transaction_currency | service_type |
|------------------|----------------|--------------------------|------------------------------|----------------------|--------------|
| CA JONES         | 123456         | JONES-NORMAL             | JNSNRML                      | USD                  | MANAGED      |
| CA JONES         | 123456         | JONES-NORMAL             | JNSNRML                      | USD                  | SELF         |
| CA JONES         | 123456         | JONES-EXTRA              | JNSXTRA                      | USD                  | MANAGED      |
| CA JONES         | 123456         | JONES-EXTRA              | JNSXTRA                      | USD                  | ALL-INCLUDED |

根据这个表的数据，我需要构建一个json字符串，看起来像这样：

{
"service_provider": "CA JONES",
"service_code" : ["JONES-NORMAL", "JONES-EXTRA"],
"correlation_id": "123456",
"available_service_data": [
{
"service_transaction_code": "JONES-NORMAL",
"did_service_transaction_code": "JNSNRML",
"transaction_currency": "USD",
"service_datasets": [
{"service_type": "MANAGED"},
{"service_type": "SELF"}
]
},
{
"service_transaction_code": "JONES-EXTRA",
"did_service_transaction_code": "JNSXTRA",
"transaction_currency": "USD",
"service_datasets": [
{"service_type": "MANAGED"},
{"service_type": "ALL-INCLUDED"}
]
}
]
}

其思想是基于correlation_id来获取数据。如何从该表中选择多个值作为单个值？例如，有可能做这样的事情吗：

SELECT service_transaction_code AS service_code
FROM available_service_information
WHERE correlation_id = '123456'

available_service_data和service_dataset也是如此。我想有一个这样的查询：

SELECT DISTINCT
service_provider,
service_transaction_code AS service_code,
correlation_id,
(SELECT DISTINCT
service_transaction_code,
did_service_transaction_code,
transaction_currency,
(SELECT DISTINCT
service_type
FROM available_service_information
WHERE service_transaction_code = 'service_transaction_code selected in the previous query'
AND did_service_transaction_code = 'did_service_transaction_code selected in the previous query'
AND transaction_currency = 'transaction_currency selected in the previous query'
AS service_datasets)
AS available_service_data
FROM available_service_information
WHERE correlation_id = '123456')
FROM available_service_information

我如何在PostgreSQL中实现这一点？

此外，如果我想将service_code存储到这个带有多个service_transaction_code的表中，我该怎么做(我的意思是将其存储为带有服务事务代码的列表(。我需要将其存储为jsonb还是有其他选项？

Answer 1

试试这个：

SELECT jsonb_build_object( 'service_provider', r.service_provider
, 'service_code', array_agg(DISTINCT r.service_transaction_code)
, 'correlation_id', r.correlation_id 
, 'available_service_data', array_agg(r.arr)
)
FROM
(
SELECT correlation_id
, service_transaction_code
, service_provider
, transaction_currency
, jsonb_build_object( 'service_transaction_code', service_transaction_code
, 'did_service_transaction_code', did_service_transaction_code
, 'transaction_currency', transaction_currency
, 'service_datasets', array_agg(jsonb_build_object('service_type', service_type))
) AS arr
FROM available_service_information
GROUP BY correlation_id, service_transaction_code, service_provider, did_service_transaction_code, transaction_currency
) AS r
GROUP BY r.correlation_id, r.service_provider, r.correlation_id 

结果：

{ "service_code": ["JONES-EXTRA", "JONES-NORMA"]
, "correlation_id": 123456
, "service_provider": "CA JONES"
, "available_service_data": [ {"service_datasets": [ {"service_type": "MANAGED"}
, {"service_type": "SELF"}
]
, "transaction_currency": "USD"  
, "service_transaction_code": "JONES-NORMA"
, "did_service_transaction_code": "JNSNRML"
}
, {"service_datasets": [ {"service_type": "MANAGED"}
, {"service_type": "ALL-INCLUDED"}
]
, "transaction_currency": "USD"
, "service_transaction_code": "JONES-EXTRA"
, "did_service_transaction_code": "JNSXTRA"
}
]
}

请参阅dbfiddle 中的测试