PostgreSQL合并多数组列并转换为指定格式JSON的查询方法

问题背景 #

现有PostgreSQL表test_table，结构如下：

create table test_table
(
  id            integer ,
  name          varchar(50),
  dob           date,
  user_id       int[],
  title         varchar[]
);

表中数据：

需要查询生成指定格式的JSON，但原查询中Links部分不符合预期，原查询语句：

select json_build_object('section1', json_build_object('ID', id),
                         'section2', json_build_object('Name', name),
                         'Date of Birth', dob,
                          'Links', json_build_array(json_build_object('UserId', user_id,
                                                                      'Title', title
                                                                     ) 
                                                   )
                       ) from test_table;

问题原因 #

原查询直接将整个user_id和title数组传入json_build_object，导致生成的是包含数组的对象，而非将两个数组的元素一一配对生成对象数组。

正确查询方法 #

需要使用unnest同时展开两个数组，将对应位置的元素配对，再通过json_agg聚合生成目标数组：

select 
  json_build_object(
    'section1', json_build_object('ID', t.id),
    'section2', json_build_object('Name', t.name),
    'Date of Birth', t.dob,
    'Links', (
      select json_agg(json_build_object('UserId', u.user_id, 'Title', u.title))
      from unnest(t.user_id, t.title) as u(user_id, title)
    )
  ) as result_json
from test_table t;

说明 #

• unnest(t.user_id, t.title)会将两个数组按索引位置一一对应展开，生成每行包含一个user_id和对应title的临时结果集
• json_agg将临时结果集中的每个json_build_object生成的对象聚合为JSON数组
• 外层的json_build_object组合所有部分得到最终的JSON结构

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