PostgreSQL中jsonb[]类型列插入语法、使用疑问及报错解决方案

直接插入字面量数据 #

使用ARRAY关键字声明PostgreSQL原生数组，数组的每个元素显式转换为jsonb类型，示例如下（对应你提供的测试数据）：

INSERT INTO 你的表名 (jsonb数组列名)
VALUES (
  ARRAY[
    '{"SchoolsCode": "","SchoolsName": "","SchoolsType": "High School","ErrorMessage": null,"SchoolsDegree": "Doctoral Degree","BiskDocumentID": "","FailedAttempts": 0,"SchoolsDegreeID": 2,"ChecklistsHidden": "Active","ChecklistsStatus": "Received","ChecklistsSection": "Official Transcript","ChecklistsSubject": "High School Transcript (Bloomingdale High School)","SchoolMailingAddress": {"City": "Windham","Region": "ME","Country": "United States","ZipCode": "","AddressStreet1": "406 Gray Rd","AddressStreet2": null},"SchoolsConferredDate": "2018-05-01","DocumentInformationID": 1,"SchoolsAttendedToDate": "2018-07-01","SchoolsAttendedFromDate": "2014-04-01","IsSalesforceUpsertSuccess": true,"IsStatusReceivedFromMaterialsEndpoint": true}'::jsonb,
    '{"SchoolsCode": "","SchoolsName": "Bloomingdale High Scho","SchoolsType": "High School","ErrorMessage": null,"SchoolsDegree": "Doctoral Degree","BiskDocumentID": "","FailedAttempts": 0,"SchoolsDegreeID": 6,"ChecklistsHidden": "Active","ChecklistsStatus": "Received","ChecklistsSection": "Official Transcript","ChecklistsSubject": "","SchoolMailingAddress": {"City": "Windham","Region": "ME","Country": "United States","ZipCode": "","AddressStreet1": "406 Gray Rd","AddressStreet2": null},"SchoolsConferredDate": "2018-05-01","DocumentInformationID": 2,"SchoolsAttendedToDate": "2018-07-01","SchoolsAttendedFromDate": "2014-04-01","IsSalesforceUpsertSuccess": true,"IsStatusReceivedFromMaterialsEndpoint": true}'::jsonb,
    '{"SchoolsCode": "","SchoolsName": "Governors State University","SchoolsType": "High School","ErrorMessage": null,"SchoolsDegree": "Bachelor''s Degree","BiskDocumentID": "fwafhawolef","FailedAttempts": 0,"SchoolsDegreeID": 4,"ChecklistsHidden": "Active","ChecklistsStatus": "Missing","ChecklistsSection": "Official Transcript","ChecklistsSubject": "High School Transcript (Bloomingdale High School)","SchoolMailingAddress": {"City": "Windham","Region": "ME","Country": "United States","ZipCode": "04062","AddressStreet1": "406 Gray Rd","AddressStreet2": null},"SchoolsConferredDate": "2018-05-01","DocumentInformationID": 3,"SchoolsAttendedToDate": "2018-07-01","SchoolsAttendedFromDate": "2014-04-01","IsSalesforceUpsertSuccess": true,"IsStatusReceivedFromMaterialsEndpoint": true}'::jsonb
  ]
);

从存储JSON数组的jsonb列迁移数据 #

你遇到的Malformed Array Literals : must introduce explicitly specified array dimension报错，是因为JSON格式的数组和PostgreSQL原生数组是完全不同的两种结构，不能直接赋值，需要先把JSON数组拆分为单个jsonb元素，再聚合为原生jsonb数组，写法如下：

-- 迁移更新场景
UPDATE 目标表 t
SET jsonb数组列名 = ARRAY(SELECT jsonb_array_elements(t.jsonb类型列名));

-- 跨表插入场景
INSERT INTO 目标表 (jsonb数组列名)
SELECT ARRAY(SELECT jsonb_array_elements(源表.jsonb类型列名))
FROM 源表;

二者的核心区别是：jsonb类型存储的数组是JSON数据结构的一部分，而jsonb[]是PostgreSQL原生的数组类型，数组的每个元素是独立的jsonb对象，适用场景不同：

• 性能差异：jsonb[]可以直接使用PostgreSQL原生数组的GIN索引，也可以针对数组元素的指定JSON键建立索引，做元素级别的查询（比如查询所有包含ChecklistsStatus = 'Missing'元素的行）性能远高于jsonb存数组的方案。
• 操作便捷性：jsonb[]可以直接使用PostgreSQL原生数组操作符，比如@>判断包含、||追加元素、[下标]直接取对应位置的元素，不需要额外调用JSON解析函数，查询写法更简洁。
• 约束支持：jsonb[]可以直接添加数组长度、元素唯一性等原生约束，jsonb存数组需要编写额外的JSON校验函数才能实现同等约束。
• 如果你需要把数组整体作为JSON结构对外输出，或者数组需要嵌套在其他JSON结构中，使用jsonb存数组会更方便，不需要额外做格式转换。

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