PostgreSQL数据库中如何过滤嵌套JSON内List2的f1=1.5数据？

现有嵌套JSON结构如下：

{"List1": [{"f1": "b6ff","f2": "day","f3": "HO","List2": [{"f1": 1.5,"f2": "RATE"}]}]}

需要筛选出List2中f1等于1.5的数据，尝试使用@>包含运算符但对嵌套JSON数组无效，该如何实现？

方法1：展开数组逐层筛选（通用方案） #

如果使用PostgreSQL，可通过jsonb_array_elements函数逐层展开嵌套数组，再精准匹配条件：

假设你的表名为your_table，存储JSON的字段名为data（类型建议用jsonb），执行以下SQL：

-- 提取符合条件的List2单条数据
SELECT 
  elem2 AS matched_list2_item
FROM 
  your_table,
  jsonb_array_elements(data->'List1') AS elem1,  -- 展开List1数组
  jsonb_array_elements(elem1->'List2') AS elem2   -- 展开每个List1元素下的List2数组
WHERE 
  (elem2->>'f1')::numeric = 1.5;  -- 匹配f1=1.5，注意转换数值类型

方法2：JSON路径查询（PostgreSQL 12+） #

如果你的PostgreSQL版本在12及以上，可以用更简洁的JSON路径语法直接查询：

SELECT 
  jsonb_path_query(data, '$.List1[*].List2[*] ? (@.f1 == 1.5)') AS matched_item
FROM 
  your_table;

这里的路径说明：

• $.List1[*]：遍历List1中的所有元素
• List2[*]：遍历每个List1元素下的List2数组
• ? (@.f1 == 1.5)：筛选出f1值等于1.5的项

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