如何合并PostgreSQL两个JSON查询结果并获取指定输出？

我在PostgreSQL中有两张表：

• regions表：字段为code_id、region
• Passenger transportation表：字段包括"Total thousands of people"、"Unit of measure"、"Date of report"、transport、Railway transport、Air transport、sea transport、Inland sea transport

我已经编写了两个查询：

查询1（获取客运数据JSON） #

SELECT json_build_object('Passenger transportation',
json_agg(
json_build_object ('Total thousands of people', "Total thousands of people", 'Unit of measure', "Unit of measure",
'Date of report', "Date of report", 'transport', "transport",
'Railway transport', "Railway transport", 'Air transport', "Air transport",
'sea transport', "sea transport", 'Inland sea transport', "Inland sea transport"
)
)
)
FROM "1.0"

查询2（获取区域列表JSON） #

SELECT json_build_object('List of regions',
                            json_agg(
                               json_build_object ('Region name', region, 'region code', kod_id)
                            )
                        )
FROM regions;

现有输出 #

查询1输出：

{
  "Passenger transportation": [
    {
      "Total thousands of people": 17565,
      "Unit of measure": "Thousands of people",
      "Date of report": "2023-06-01",
      "transport": 2423,
      "Railway transport": 421,
      "Air transport": 234,
      "sea transport": 54,
      "Inland sea transport": 23
    }
 ]
}

查询2输出：

[
{
   "List of regions":[
      {
         "Region name":"Moscow region",
         "region code":77
      },
      {
         "Region name":"Belgorod Oblast",
         "region code":31
      },
      {
         "Region name":"Bryansk Oblast",
         "region code":32
      }
   ]
}
]

期望输出 #

我需要得到如下格式的JSON响应（每条客运记录对应一个包含区域列表和该记录的JSON对象）：

第一条结果：

{
   "List of regions":[
      {
         "Region name":"Moscow region",
         "region code":77
      },
      {
         "Region name":"Belgorod Oblast",
         "region code":31
      }
   ],
   "Passenger transportation": [
      {
         "Total thousands of people": 17565,
         "Unit of measure": "Thousands of people",
         "Date of report": "2023-06-01",
         "transport": 2423,
         "Railway transport": 423,
         "Air transport": 234,
         "sea transport": 54,
         "Inland sea transport": 23
      }
   ]
}

第二条结果：

{
   "List of regions":[
      {
         "Region name":"Moscow region",
         "region code":77
      },
      {
         "Region name":"Belgorod Oblast",
         "region code":31
      }
   ],
   "Passenger transportation": [
      {
         "Total thousands of people": 17566,
         "Unit of measure": "Thousands of people",
         "Date of report": "2023-06-02",
         "transport": 2425,
         "Railway transport": 423,
         "Air transport": 554,
         "sea transport": 55,
         "Inland sea transport": 13
      }
   ]
}

请问：

1. 如何编写查询获取上述期望输出？
2. 哪里可以找到PostgreSQL JSON输出的示例查询？

1. 生成期望格式的查询 #

首先修正两个问题：

• 查询2中写错了字段名：regions表的字段是code_id，你写成了kod_id，需要修正。
• 查询1中使用的表名"1.0"应该是"Passenger transportation"的笔误，需替换。

注：你给出的期望输出存在JSON语法错误（两个顶级对象直接用逗号连接，不符合JSON规范），以下查询生成的是合法的JSON对象，包含List of regions和Passenger transportation两个键，结构和你需求的语义一致。

-- 先预计算区域列表的JSON数据
WITH regions_json AS (
  SELECT json_agg(json_build_object('Region name', region, 'region code', code_id)) AS region_list
  FROM regions
  -- 如果需要筛选特定区域（比如只保留Moscow和Belgorod），可以加WHERE条件：
  -- WHERE region IN ('Moscow region', 'Belgorod Oblast')
)
SELECT json_build_object(
  'List of regions', (SELECT region_list FROM regions_json),
  'Passenger transportation', json_build_array(
    json_build_object(
      'Total thousands of people', "Total thousands of people",
      'Unit of measure', "Unit of measure",
      'Date of report', "Date of report",
      'transport', "transport",
      'Railway transport', "Railway transport",
      'Air transport', "Air transport",
      'sea transport', "sea transport",
      'Inland sea transport', "Inland sea transport"
    )
  )
)
FROM "Passenger transportation";

说明 #

• 使用WITH子句预计算区域列表的JSON数组，避免重复计算。
• 通过json_build_array将单条客运记录包装成数组，和你期望的格式一致。
• 如果需要筛选区域，在regions_json的FROM regions后添加WHERE条件即可。

2. PostgreSQL JSON查询示例来源 #

PostgreSQL官方文档的JSON函数与操作符章节包含了大量JSON输出相关的示例查询，涵盖json_build_object、json_agg、jsonb系列函数等的用法，以及多种JSON结构生成的案例。你可以直接查阅对应你使用版本的PostgreSQL文档来获取详细示例。

内容的提问来源于stack exchange，提问作者Nail