PostgreSQL查询ordinality position返回NULL的处理方法咨询

问题根因 #

你的过滤条件写在WHERE子句中，当JSON数组中不存在同时满足elem->>'userType'='employee'和elem->>'userState'='onboarding pending'的元素时，整行数据会被直接过滤，结果集为空，并非返回的行中pos字段为NULL，所以你在SELECT层加coalesce、CASE这类NULL处理逻辑自然不会生效。

解决方案 #

方案1：改用LEFT JOIN，过滤条件挪到JOIN子句 #

该方案适合需要保留所有users表行，同时返回所有匹配pos的场景，没有匹配项时pos会返回你设置的默认值：

select coalesce(arr.pos, 0) as pos -- 0可替换为你需要的自定义默认值
from users 
  left join jsonb_array_elements(user_details->'userProfile') with ordinality arr(elem,pos)
  on elem->>'userType'='employee' 
  and elem->>'userState'='onboarding pending'

方案2：标量子查询+默认值 #

该方案适合每个用户仅需要返回第一个匹配的pos，无匹配时返回统一默认值的场景：

select coalesce(
  (
    select pos
    from jsonb_array_elements(user_details->'userProfile') with ordinality arr(elem,pos)
    where elem->>'userType'='employee' 
      and elem->>'userState'='onboarding pending'
    limit 1
  ),
  0 -- 可替换为自定义默认值
) as pos
from users

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