PostgreSQL高效筛选jsonb字段为数组的异常数据

嘿，刚好碰到过类似的大表jsonb类型校验问题，给你几个高效的解决方案，完全不用走转文本这种低效路子：

核心方案：用jsonb_typeof()精准判断内部类型 #

你之前用的pg_typeof()是返回字段本身的数据类型（所以不管存的是对象还是数组，都只会返回jsonb），而PostgreSQL专门提供了jsonb_typeof()函数来判断jsonb字段内部存储的数据类型——它会返回'object'（对应你说的字典）、'array'（数组）、'string'、'number'这些值，完美解决你的问题！

针对2亿条数据的大表，直接用这个函数写查询就非常高效，甚至可以建表达式索引进一步加速：

基础查询语句

SELECT *
FROM your_table
WHERE jsonb_typeof(your_jsonb_column) = 'array';

优化：创建表达式索引（可选但推荐）

如果这个查询需要频繁执行，建个表达式索引能把查询速度拉满——因为索引只存类型字符串，体积很小，维护成本极低：

CREATE INDEX idx_jsonb_column_type ON your_table (jsonb_typeof(your_jsonb_column));

备选方案：利用jsonb_array_length()间接判断 #

如果不想建索引，只是临时查询，也可以利用jsonb_array_length()的特性：它对顶层是数组的jsonb返回数组长度，对顶层是对象的jsonb返回NULL，所以也能筛选出数组类型的条目：

SELECT *
FROM your_table
WHERE jsonb_array_length(your_jsonb_column) IS NOT NULL;

不过这个方法不如jsonb_typeof()直观，推荐优先用第一个方案。

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