在PostgreSQL里处理JSON数据时，经常需要从一个JSON数组中提取满足条件的元素，或者只取出数组中某个键的值。比如订单表里存了一个items字段，内容是JSON数组，每个元素包含product_id、quantity、price等键，业务上要查询quantity大于2的记录。PostgreSQL的json和jsonb类型提供了丰富的操作符和函数，本文会通过几个实际SQL示例演示如何展开数组、提取键值并做条件过滤。
json与jsonb：先搞清楚该用哪个类型
PostgreSQL支持两种JSON类型：json和jsonb。json类型只是存储原始文本，写入时不做转换，查询时需要反复解析，因此性能较差，也不支持索引。jsonb则会把JSON解析成二进制格式，键会被排序，重复键只保留最后一个，查询时无需重新解析，支持GIN索引，更适合频繁查询的场景。如果你的JSON数组需要做条件筛选，优先使用jsonb。
举个例子，建表时把items列定义成jsonb：
CREATE TABLE orders (
id serial PRIMARY KEY,
order_info jsonb
);
插入一条包含JSON数组的数据：
INSERT INTO orders (order_info) VALUES
('{"order_id": 1001, "items": [{"product_id": 1, "quantity": 3, "price": 19.9}, {"product_id": 2, "quantity": 1, "price": 45.0}]}');
以上代码中，order_info字段本身的JSON对象里有一个items键，其值是一个数组，数组元素是包含product_id、quantity、price的对象。接下来所有查询都围绕这个结构展开。
用jsonb_array_elements展开JSON数组
jsonb_array_elements是处理JSON数组的核心函数。它接收一个jsonb数组，返回多行，每行对应数组中的一个元素。比如要查看items数组中的每个元素，可以这样写：
SELECT jsonb_array_elements(order_info->'items') AS item FROM orders WHERE order_info->>'order_id' = '1001';
这里使用->操作符按键获取jsonb对象中的items值，该值仍然是jsonb类型。jsonb_array_elements函数把这个数组拆成多行，每一行是一个jsonb对象。但要注意，如果items键不存在或者值为NULL，函数不会返回任何行。如果需要保留原行，可以改用LEFT JOIN LATERAL配合jsonb_array_elements。
展开之后如果想提取元素内部的某个键，需要继续使用->>操作符。例如只想要每个商品的product_id和quantity，可以写成：
SELECT
item->>'product_id' AS product_id,
item->>'quantity' AS quantity
FROM orders,
jsonb_array_elements(order_info->'items') AS item;
这段SQL使用了隐式LATERAL，把jsonb_array_elements放在FROM子句中，并给返回的列起别名item。然后通过item->>'product_id'取出JSON对象中product_id键的文本值。注意->>返回的是text类型而不是jsonb，所以后续比较时可以直接与字符串或数字文本比较。
筛选特定键值：WHERE条件怎么写
展开数组的主要目的之一就是过滤。假设要查询quantity大于2的商品记录，需要在WHERE子句中引用展开后的元素。最直接的方式是把jsonb_array_elements放在FROM子句中，然后在WHERE里对提取的键值做判断：
SELECT
order_info->>'order_id' AS order_id,
item->>'product_id' AS product_id,
item->>'quantity' AS quantity
FROM orders,
jsonb_array_elements(order_info->'items') AS item
WHERE (item->>'quantity')::int > 2;
因为->>返回text，所以需要先用::int转换成整数，再和数字比较。如果不转换，text和integer之间不能直接比较，会报类型错误。类似地，如果想筛选price大于20的商品，可以写(item->>'price')::numeric > 20。
另一种常用写法是在SELECT列表中使用jsonb_path_query或jsonb_array_elements，但FROM子句加WHERE是最清晰、可读性最好的方式。如果需要同时筛选多个条件，可以用AND连接，比如quantity大于2且price小于50：
SELECT
order_info->>'order_id' AS order_id,
item->>'product_id' AS product_id
FROM orders,
jsonb_array_elements(order_info->'items') AS item
WHERE (item->>'quantity')::int > 2
AND (item->>'price')::numeric < 50;
这里涉及数值范围判断，代码块中>和<已做HTML转义，但在实际SQL中就是大于号和小于号。这种写法能精确筛选出符合条件的数组元素，返回的每一行对应一个满足条件的数组元素。
处理不存在的键和NULL值
JSON数据往往不规范，有些元素可能缺少某个键，或者键的值为null。直接使用item->>'quantity'如果键不存在，会返回NULL。在WHERE条件中，NULL参与比较时结果既不是true也不是false，而是NULL，导致该行被过滤掉。如果希望保留缺失键的行，需要显式处理NULL。
例如某些商品没有记录quantity字段，我们希望把它们当作0来处理，可以使用COALESCE函数：
SELECT
item->>'product_id' AS product_id,
COALESCE((item->>'quantity')::int, 0) AS quantity
FROM orders,
jsonb_array_elements(order_info->'items') AS item;
COALESCE接收多个参数，返回第一个非NULL值。当quantity键不存在时，转换结果为NULL，COALESCE就返回0。这样后续可以安全地进行数值比较。另一个常见需求是只查询存在某个键的元素，可以使用item ? 'quantity'操作符，它返回布尔值，表示jsonb对象中是否包含指定键。示例：
SELECT item
FROM orders,
jsonb_array_elements(order_info->'items') AS item
WHERE item ? 'quantity';
这段SQL只返回items数组中包含quantity键的元素，忽略掉没有该键的元素。?操作符针对jsonb对象使用，检查键是否存在于顶层，不能检查嵌套路径。如果需要检查嵌套键，可以结合#>等路径操作符。
利用索引提升JSON数组查询性能
当orders表数据量增大后，如果每次查询都全表扫描并展开数组，性能会明显下降。PostgreSQL的jsonb类型支持GIN索引，可以对JSON中的键和值建立索引，加速包含、存在等查询。创建索引的语法如下：
CREATE INDEX idx_orders_order_info ON orders USING GIN (order_info);
不过GIN索引对jsonb_array_elements这种展开操作帮助有限，它更适合@>、?等操作符。比如要查询items数组中是否存在某个product_id，可以使用@>判断数组是否包含指定对象：
SELECT * FROM orders
WHERE order_info->'items' @> '[{"product_id": 1}]'::jsonb;
这个条件表示items数组包含至少一个product_id为1的元素。配合GIN索引，这种查询可以走索引而不是全表扫描。需要注意的是，@>判断的是结构包含，数组元素的对象必须完全匹配提供的片段，也就是说如果提供的对象里还有其他键，则不会匹配。实际使用时尽量提供完整的元素对象或使用jsonb_path_ops索引。
对于需要频繁按某个键值筛选的场景，还可以在生成列上建立B-tree索引。例如把quantity提取成独立的生成列，然后在该列上建索引：
ALTER TABLE orders ADD COLUMN quantity int GENERATED ALWAYS AS ((order_info->'items'->0->>'quantity')::int) STORED; CREATE INDEX idx_orders_quantity ON orders (quantity);
这里仅示意提取数组第一个元素的quantity，实际中可能需要维护多行关系表，或者使用jsonb_to_recordset把数组拆成关系表。生成列配合索引适合查询条件固定且数据结构稳定的场景。
对比json_array_elements与jsonb_array_elements
PostgreSQL还提供了json_array_elements函数用于json类型。它与jsonb_array_elements的用法基本相同，但接收json数组而不是jsonb数组。由于json类型不解析内容，每次调用json_array_elements都需要重新解析JSON文本，性能低于jsonb版本。因此除非历史原因必须使用json类型，否则新项目应统一使用jsonb。
两者在返回类型上也不同：json_array_elements返回json，jsonb_array_elements返回jsonb。后续提取键值时，json版本仍然可以用->>操作符，但操作过程中存在隐式转换。从执行计划看，jsonb版本通常更高效，因为解析只发生一次。下面给出json版本的等价写法以便对比：
SELECT
item->>'product_id' AS product_id
FROM orders,
json_array_elements(order_info::json->'items') AS item;
注意order_info如果是jsonb类型，需要先::json转换成json，否则函数无法匹配。实际开发中不建议这样来回转换，直接使用jsonb_array_elements即可。
总结一下，PostgreSQL查询JSON数组并筛选特定键值，核心步骤是：用->取出数组，用jsonb_array_elements展开为行，再用->>提取键值并在WHERE中做类型转换和比较。配合GIN索引和COALESCE处理NULL，可以覆盖大多数实际业务场景。理解json和jsonb的区别以及操作符的返回类型，能避免很多不必要的类型错误。
PostgreSQLJSON数组jsonb_array_elements修改时间：2026-10-03 07:52:52