PostgreSQL中用json_array_elements提取JSON对象数组字段报错解决

问题背景 #

创建了如下临时表存储JSON格式文本：

DROP TABLE IF EXISTS tmp2;
CREATE TEMP table tmp2 (
      c TEXT
    );

insert into tmp2 values
(        
N'{"SerialNumber":"907578","SoftwareVersion":"1.2.777","Build":"4829","ProductCode":"TR-3500-A","BuildDate":null,"Description":"Study desk","ConnectionType":2,"Capabilities":[2,3],
        "ChannelListDto":[
            {"ChannelId":0,"ConversionType":0,"DeviceSerialNumber":null,"Dimension":"","FixedName":null,"Name":"test2","InstrumentationChannel":-1,"IsAlarmable":false,"IsInternal":true,"IsEnableable":false,"IsEnabled":false,"JournalledReadingBytes":0,"LowerLimit":null,"Precision":null,"Symbol":"","TypeId":5,"UpperLimit":null},
            {"ChannelId":1,"ConversionType":0,"DeviceSerialNumber":null,"Dimension":"","FixedName":null,"Name":null,"InstrumentationChannel":-1,"IsAlarmable":false,"IsInternal":true,"IsEnableable":false,"IsEnabled":false,"JournalledReadingBytes":0,"LowerLimit":null,"Precision":null,"Symbol":"","TypeId":5,"UpperLimit":null},
            {"ChannelId":2,"ConversionType":0,"DeviceSerialNumber":null,"Dimension":"","FixedName":null,"Name":null,"InstrumentationChannel":-1,"IsAlarmable":false,"IsInternal":true,"IsEnableable":false,"IsEnabled":false,"JournalledReadingBytes":0,"LowerLimit":null,"Precision":null,"Symbol":"","TypeId":5,"UpperLimit":null}
         ]
        }'
)

错误尝试与报错 #

尝试用json_array_elements提取ChannelListDto数组字段时，执行了以下SQL：

select c::json ->> 'SerialNumber' as SerialNumber,
       cto ->> 'ChannelId'::INT   AS channelid,
       cto ->> 'ChannelName'      AS ChannelName
from tmp2
CROSS JOIN LATERAL json_array_elements(c::json ->'ChannelListDto') Channels(cto);

出现错误：

[2024-01-11 17:54:14] [22P02] ERROR: invalid input syntax for type integer: "ChannelId"
[2024-01-11 17:54:14] Position: 122

期望结果 #

DeviceSerialNumber channelID   ChannelName                             IsInternal        
------------------ ----------- --------------------------------------- ----------- 
907578             0           test2                                   1          
907578             1                                                   1           
907578             2                                                   1       

解决方案 #

错误原因有两点：

1. 类型转换位置错误：::INT应放在整个字段提取表达式后，而非字段名'ChannelId'后，否则会尝试将字符串"ChannelId"转为整数，触发语法错误。
2. 字段名不匹配：JSON中无ChannelName字段，对应实际字段为Name；IsInternal是布尔值，可通过::INT转为1/0格式。

正确SQL如下：

select 
    c::json ->> 'SerialNumber' as DeviceSerialNumber,
    (cto ->> 'ChannelId')::INT AS channelid,
    cto ->> 'Name' AS ChannelName,
    (cto ->> 'IsInternal')::INT AS IsInternal,
    (cto ->> 'ConversionType')::INT AS ConversionType
from tmp2
CROSS JOIN LATERAL json_array_elements(c::json -> 'ChannelListDto') Channels(cto);

执行后即可得到期望结果，若需提取其他字段（如ConversionType），按相同逻辑处理即可：先提取字段值，再按需转换类型。

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