PostgreSQL：如何在WHERE子句正确使用jsonb_array_length？

一、正确筛选node2为数组且长度>1的记录 #

你遇到的非数组无法获取长度的错误，本质是PostgreSQL的表达式求值顺序问题——即使WHERE子句里写了类型判断，数据库可能会先执行jsonb_array_length计算，导致非数组行触发报错。解决方法是确保只对已确认是数组的行计算长度，有两种可靠写法：

方法1：用子查询先过滤数组类型 #

SELECT *
FROM (
  SELECT 
    *, 
    payload #> '{node1, node2}' AS target_node  -- 用路径操作符直接定位node2
  FROM your_table
  WHERE jsonb_typeof(payload #> '{node1, node2}') = 'array'  -- 先过滤出数组类型的行
) filtered
WHERE jsonb_array_length(target_node) > 1;

方法2：用CASE表达式避免提前计算长度 #

如果不想用子查询，也可以用CASE在计算长度时做防护：

SELECT *
FROM your_table
WHERE jsonb_typeof(payload -> 'node1' -> 'node2') = 'array'
  AND CASE 
        WHEN jsonb_typeof(payload -> 'node1' -> 'node2') = 'array' 
        THEN jsonb_array_length(payload -> 'node1' -> 'node2') 
        ELSE 0 
      END > 1;

注意：如果node1可能不存在或不是对象，payload -> 'node1' -> 'node2'会返回null，jsonb_typeof(null)不等于'array'，这类行会被自动过滤，无需额外处理。

二、::text在JSONB路径中的作用机制 #

(payload-> 'node1'::text)-> 'node2'::text里的::text是PostgreSQL的显式类型转换语法，作用是把右侧的键名强制转为text类型。但实际上PostgreSQL中，单引号包裹的字符串字面量（比如'node1'）默认就是text类型，所以这里的::text完全是冗余操作，不会改变键名的内容或匹配逻辑。

你遇到“加::text无结果、去掉后正常”的情况，大概率是操作失误导致的：比如转换时不小心加了空格（如'node1':: text）、键名拼写错误，或者误将->（获取JSONB对象）写成了->>（获取文本值）。正常情况下，加不加::text对字符串键名的匹配没有影响。

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