如何优化PostgreSQL中JSON数据解析为关系表的SQL语句？

问题背景 #

给定如下JSON结构：

{
    "obj": {
        "a": "x",
        "b": "y"
    },
    "arr": [
        {
            "c1": 11,
            "c2": 12,
            "c3": 13
        },
        {
            "c1": 21,
            "c2": 22,
            "c3": 23
        }
    ]
}

需要将其解析为PostgreSQL中的关系表，目标结构如下：

a b c1 c2 c3 
------------
x y 11 12 13 
x y 21 22 23

当前实现代码为：

create table test(data json);
insert into test(data) values ('{"obj": {"a": "x","b": "y"},"arr": [{"c1": 11,"c2": 12,"c3": 13},{"c1": 21,"c2": 22,"c3": 23}]}')

select 
    (data -> 'obj' ->> 'a'),
    (data -> 'obj' ->> 'b'),
    (json_array_elements(data -> 'arr') ->> 'c1'),
    (json_array_elements(data -> 'arr') ->> 'c2'),
    (json_array_elements(data -> 'arr') ->> 'c3')
from test

该写法对数组内每个字段都调用一次json_array_elements，当数组内对象包含大量键值对时，代码冗余且效率低下，需要更优方案。

优化方案：使用LATERAL JOIN单次展开数组 #

通过LATERAL JOIN配合json_array_elements，只需一次展开数组中的元素，再从展开后的元素中提取所有需要的字段，既简化代码，又避免重复解析数组，提升性能。

优化后的SQL代码：

create table test(data json);
insert into test(data) values ('{"obj": {"a": "x","b": "y"},"arr": [{"c1": 11,"c2": 12,"c3": 13},{"c1": 21,"c2": 22,"c3": 23}]}')

select 
    data -> 'obj' ->> 'a' as a,
    data -> 'obj' ->> 'b' as b,
    arr_item ->> 'c1' as c1,
    arr_item ->> 'c2' as c2,
    arr_item ->> 'c3' as c3
from test,
     json_array_elements(data -> 'arr') as arr_item;

方案优势 #

• 代码更简洁：无需为每个字段重复调用json_array_elements，尤其当数组内字段数量较多时，维护成本大幅降低
• 性能更优：仅需一次解析数组并展开元素，避免多次重复解析同一数组带来的额外开销
• 扩展性更好：后续新增数组内的字段，只需在SELECT中添加对应arr_item ->> '字段名'的语句即可

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