如何让jsonb_array_elements过滤结果与筛选数组顺序一致？

我执行了以下SQL查询：

SELECT id, listing -> 'description' AS description, listing -> 'vin' AS vin
FROM public.listings,
     jsonb_array_elements(data -> 'attributes' -> 'listings') listing
WHERE id = '12070'
  AND listing -> 'vin' ?| array ['0HSDZTZR9LN000000','3HSDZTZR9LN056080']

得到的结果为：

12070,"Pie","3HSDZTZR9LN056080"
12070,"Soda","0HSDZTZR9LN000000"

请问是否可以让查询结果与筛选数组['0HSDZTZR9LN000000','3HSDZTZR9LN056080']的顺序保持一致？

可以实现，核心是利用PostgreSQL的array_position函数，根据筛选数组中元素的位置排序结果。

需要注意两个细节：

• 用->>操作符替代->获取vin的文本值（->返回jsonb类型，->>返回text类型，能直接和数组中的字符串匹配）
• 在ORDER BY子句中通过array_position计算当前vin在筛选数组中的位置，按该位置排序

修改后的SQL如下：

SELECT id, listing ->> 'description' AS description, listing ->> 'vin' AS vin
FROM public.listings,
     jsonb_array_elements(data -> 'attributes' -> 'listings') listing
WHERE id = '12070'
  AND listing ->> 'vin' = ANY(array ['0HSDZTZR9LN000000','3HSDZTZR9LN056080'])
ORDER BY array_position(array ['0HSDZTZR9LN000000','3HSDZTZR9LN056080'], listing ->> 'vin');

说明： #

1. array_position(目标数组, 元素)返回元素在数组中的索引（从1开始），例如0HSDZTZR9LN000000的位置是1，3HSDZTZR9LN056080的位置是2
2. 按该位置排序后，结果会严格遵循筛选数组的顺序

如果不想重复编写筛选数组，可通过CTE复用：

WITH filter_cte AS (
  SELECT array ['0HSDZTZR9LN000000','3HSDZTZR9LN056080'] AS target_vins
)
SELECT l.id, listing ->> 'description' AS description, listing ->> 'vin' AS vin
FROM public.listings l,
     jsonb_array_elements(l.data -> 'attributes' -> 'listings') listing,
     filter_cte
WHERE l.id = '12070'
  AND listing ->> 'vin' = ANY(filter_cte.target_vins)
ORDER BY array_position(filter_cte.target_vins, listing ->> 'vin');

调整后，查询结果将按照0HSDZTZR9LN000000在前、3HSDZTZR9LN056080在后的顺序返回。

内容的提问来源于stack exchange，提问作者Nicolás González