PostgreSQL中如何为嵌套JSONB的非空数组条件创建索引？

我有一张名为orders的表，其中包含一个名为data的列，数据类型为jsonb，结构示例如下（修正了原始JSON的语法错误）：

"data": [
  {
    "items": [
     {"name": "Peter"},
     {"name": "John"}
    ]
  }
]

当前用于获取唯一名称列表的查询语句：

SELECT distinct NameList->'name' AS uniqueNames
FROM orders
         CROSS JOIN jsonb_array_elements(data) itemsData
         CROSS JOIN jsonb_array_elements(itemsData->'items') NameList
WHERE NameList != '[]'

我只关注data列里的items部分，想为NameList != '[]'这个条件创建索引，该怎么做？

首先明确：你的WHERE NameList != '[]'核心是过滤有效items元素，下面分两种场景给出实现方案：

场景1：过滤items数组不为空的记录 #

如果真实需求是只处理data数组中items不为空的元素，先优化查询逻辑，再创建对应索引：

优化后的查询 #

把过滤条件提前到外层，减少不必要的展开操作：

SELECT DISTINCT item->>'name' AS uniqueNames
FROM orders,
     jsonb_array_elements(data) AS d,
     jsonb_array_elements(d->'items') AS item
WHERE d->'items' != '[]'

（注：用->>代替->可以直接获取不带引号的文本值，结果更符合预期）

创建表达式索引 #

针对data列中items非空的判断，创建精准的表达式索引：

CREATE INDEX idx_orders_data_items_non_empty ON orders ((data @> '[{"items": []}]') NOT);

该索引直接存储data数组中存在非空items的记录标识，查询时可快速命中符合条件的行。

如果需要更灵活的JSON路径查询，也可以创建GIN索引：

CREATE INDEX idx_orders_data_gin ON orders USING GIN (data jsonb_path_ops);

场景2：过滤展开后的单个元素不为空数组 #

如果items中确实存在空数组元素，由于过滤条件是JOIN展开后的结果，无法直接创建普通索引，建议使用物化视图预先处理数据：

创建物化视图 #

CREATE MATERIALIZED VIEW orders_items AS
SELECT id, item->>'name' AS uniqueNames
FROM orders,
     jsonb_array_elements(data) AS d,
     jsonb_array_elements(d->'items') AS item
WHERE item != '[]';

-- 为物化视图创建索引加速查询
CREATE INDEX idx_orders_items_unique ON orders_items (uniqueNames);

查询物化视图 #

SELECT DISTINCT uniqueNames FROM orders_items;

同步源表数据 #

定期刷新物化视图以同步源表的更新：

REFRESH MATERIALIZED VIEW orders_items;

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