PostgreSQL数组列查询：插入对象数组返回嵌套字符串数组的原因及方法

问题根因 #

• 字段类型选择错误：你当前存储items的字段使用的是普通数组类型（如text[]），而非PostgreSQL专门用于存储结构化对象的jsonb/json类型。你直接粘贴JS格式的对象数组时，PostgreSQL无法识别对象结构，会自动将每个对象的键值对拆分为独立的字符串元素存入数组，最终返回二维字符串数组。
• 插入格式不规范：你直接复制的原始数组是JS对象简写格式，对象键没有加双引号，不符合标准JSON规范，PostgreSQL无法将其识别为结构化数据，只能按普通文本拆分存储，导致格式完全变形。

正确使用方案 #

1. 调整字段类型 #

优先选择jsonb类型存储该类数据，相比json类型支持索引、查询性能更高，修改字段类型的语句如下：

ALTER TABLE 你的表名 ALTER COLUMN items TYPE jsonb USING items::jsonb;

如果执行报错，先清空字段内已存储的错误格式数据后再执行。

2. 规范插入操作 #

插入前需将原始数组转换为标准JSON格式：所有对象键和字符串值都用双引号包裹，标准格式示例：

[
  {"product_id": "227534500", "quantity": 1},
  {"product_id": "226320600", "quantity": 1},
  {"product_id": "208913600", "quantity": 1},
  {"product_id": "225421600", "quantity": 1}
]

插入语句示例：

INSERT INTO 你的表名 (items) VALUES ('[
  {"product_id": "227534500", "quantity": 1},
  {"product_id": "226320600", "quantity": 1},
  {"product_id": "208913600", "quantity": 1},
  {"product_id": "225421600", "quantity": 1}
]'::jsonb);

3. 常见查询操作 #

查询完整数组

直接查询即可返回和原始结构一致的JSON数组：

SELECT items FROM 你的表名;

查询包含指定product_id的记录

比如筛选包含商品ID为227534500的行：

SELECT * FROM 你的表名 WHERE items @> '[{"product_id": "227534500"}]'::jsonb;

展开数组返回单个商品对象

将数组拆分为单行，每行对应一个商品对象：

SELECT jsonb_array_elements(items) AS single_item FROM 你的表名;

直接提取商品字段值

比如查询所有商品的product_id：

SELECT jsonb_array_elements(items)->>'product_id' AS product_id FROM 你的表名;

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