如何在PostgreSQL中按布尔值过滤JSON数据

假设你有一张表（例：test_table），其中存储JSON数据的字段为data（推荐用jsonb类型，比json性能更优），字段结构如下：

{
"L1": [
{
"f1": "446",
"f2": true,
"f3": true
},
{
"f1": "191",
"f2": true,
"f3": true
}
]
}

以下是两种可行的过滤方案：

方案1：展开JSON数组后过滤 #

适用于所有支持JSON的PostgreSQL版本，通过展开数组为单行记录再筛选：

SELECT 
  elem->>'f1' AS f1,
  elem->>'f2' AS f2,
  elem->>'f3' AS f3
FROM test_table,
     jsonb_array_elements(data->'L1') AS elem
WHERE 
  (elem->>'f2')::boolean = true
  AND (elem->>'f3')::boolean = true;

• jsonb_array_elements(data->'L1') 将L1数组拆分为多条行记录，每条对应一个数组对象（别名elem）
• elem->>'f2' 提取字段文本值，通过::boolean转换为布尔类型后判断是否为true

如果字段是json类型，将jsonb_array_elements替换为json_array_elements即可。

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

利用PostgreSQL 12及以上版本支持的JSON路径语法，直接在JSON结构内筛选：

SELECT 
  jsonb_path_query(data, '$.L1[*] ? (@.f2 == true && @.f3 == true)') AS filtered_item
FROM test_table;

• $.L1[*] 遍历L1数组的所有元素
• ? (@.f2 == true && @.f3 == true) 仅保留f2和f3均为true的元素

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