PostgreSQL中如何将JSONB字段的键值提取为两列多行数据

在PostgreSQL数据库中执行以下查询：

select json_field from my_table where role='addresses_line';

返回的JSONB类型结果为：

{"alpha": ["10001"], "beta": ["10002"], "gamma": ["10003"]}

需要将该JSON字段的所有键和对应数组中的值提取为两列多行的形式，期望输出：

key  |  value
----------------
alpha  |  10001
beta   |  10002
gamma  |  10003

解决方案 #

由于目标JSON中每个键对应的值都是单元素数组，可以结合jsonb_each和jsonb_array_element_text实现需求：

方式1：关联表查询 #

SELECT 
    j.key, 
    jsonb_array_element_text(j.value) AS value
FROM 
    my_table,
    jsonb_each(json_field) j
WHERE 
    role = 'addresses_line';

方式2：子查询形式 #

SELECT 
    key, 
    jsonb_array_element_text(value) AS value
FROM 
    jsonb_each(
        (SELECT json_field FROM my_table WHERE role='addresses_line')
    );

说明：

• jsonb_each函数会将JSONB对象拆分为多行的键值对记录；
• jsonb_array_element_text函数将数组类型的value转换为文本类型的单个元素，适配当前场景中每个数组仅含一个元素的情况。

泛化场景补充 #

如果JSONB字段中的值为单个非数组类型（例如返回结果为{"alpha": 10001, "beta": 10002, "gamma": 10003}），则直接使用jsonb_each即可：

select key, value
from jsonb_each
(
  (select json_field from my_table where role='addresses_line')
);

感谢@stefanov.fm提供此泛化方案。

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