PostgreSQL向jsonb列插入数组时JSON语法错误问题排查

错误原因 #

你当前的SQL写法存在两个核心问题：

• 语法错误：数组元素'Surname', surname和'GivenName', first_name之间缺少逗号，导致SQL解析失败。
• JSON构造逻辑错误：直接将零散的键字符串和字段值塞进数组再强转jsonb[]，PostgreSQL无法将单个字符串（比如'Surname'）识别为合法的JSON值，这就是报错"invalid input syntax for type json 'Surname'"的原因。你需要的是JSON对象数组，而非字符串和值的混合数组。

正确SQL写法 #

要生成你需要的{"EmpNames": [{"id": "...", "Surname": "...", ...}]}格式JSONB，应该使用PostgreSQL的JSON构造函数来逐层构建：

1. 生成包含单个员工对象的完整JSON结构 #

如果要直接生成目标格式的JSONB数据（用于插入或查询）：

SELECT jsonb_build_object(
    'EmpNames',
    jsonb_build_array(
        jsonb_build_object(
            'id', '5680', -- 若id是表中字段，替换为对应字段名，比如employees.id
            'Surname', surname,
            'GivenName', first_name,
            'MiddleName', middle_name
        )
    )
) AS emp_json
FROM stg.employees;

2. 单独生成EmpNames数组部分 #

如果只需要生成EmpNames对应的JSONB数组：

SELECT jsonb_build_array(
    jsonb_build_object(
        'id', '5680',
        'Surname', surname,
        'GivenName', first_name,
        'MiddleName', middle_name
    )
) AS emp_names_array
FROM stg.employees;

3. 插入JSONB列的示例 #

如果要插入到目标表的JSONB列（比如target_table的emp_data列）：

INSERT INTO target_table (emp_data)
SELECT jsonb_build_object(
    'EmpNames',
    jsonb_build_array(
        jsonb_build_object(
            'id', employees.id, -- 假设id是stg.employees表的字段
            'Surname', surname,
            'GivenName', first_name,
            'MiddleName', middle_name
        )
    )
)
FROM stg.employees;

关键函数说明 #

• jsonb_build_object(key1, value1, key2, value2...)：构建单个JSONB对象，键值对一一对应。
• jsonb_build_array(element1, element2...)：构建JSONB数组，将多个JSON对象或值包装成数组。

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