PostgreSQL中使用jsonpath从JSON数组筛选符合条件键的实现方法问询

问题原因 #

你之前编写的$[?("r_ppe"="oui")]无法生效有两个核心问题：

• 表达式中"r_ppe"="oui"是两个字符串常量做等值判断，结果永远为真，不会按照字段值筛选JSON元素
• jsonpath中引用当前遍历元素的字段需要用@.字段名语法，同时你没有补充后续三个目标字段的匹配、键名提取逻辑

解决方案 #

PostgreSQL中实现该需求有两种常用方式，以下基于你的示例数据给出可直接运行的代码：

方式1：使用JSON函数拆分遍历（可读性更高，灵活度更好） #

WITH sample_data AS (
    -- 这里替换为你自己的表/字段查询逻辑即可
    SELECT '[
  {
    "r_id": "00000000",
    "r_ppe": "non",
    "r_dirigeant": "non",
    "r_actionnaire": "oui",
    "r_nationality": "788",
    "r_beneficiaire": "non",
    "r_businessName": "mohamed ben zineb",
    "r_nationality2": "",
    "r_educational_level": 3
  },
  {
    "r_id": "05548914",
    "r_ppe": "oui",
    "r_dirigeant": "non",
    "r_actionnaire": "oui",
    "r_nationality": "788",
    "r_beneficiaire": "non",
    "r_businessName": "rim cherif",
    "r_nationality2": "",
    "r_educational_level": 4
  },
  {
    "r_id": "02954047",
    "r_ppe": "non",
    "r_dirigeant": "oui",
    "r_actionnaire": "oui",
    "r_nationality": "788",
    "r_beneficiaire": "oui",
    "r_businessName": "taoufik ben zineb",
    "r_nationality2": "",
    "r_educational_level": 4
  }
]'::jsonb AS json_arr
)
-- 聚合输出所有匹配的键名，可根据需要调整输出格式
SELECT ARRAY_AGG(key) AS matched_keys
FROM sample_data,
     -- 拆分JSON数组为单个元素
     jsonb_array_elements(json_arr) AS elem,
     -- 拆分单个JSON对象为键值对
     jsonb_each_text(elem) AS kv
WHERE 
  -- 筛选r_ppe为oui的元素
  elem ->> 'r_ppe' = 'oui'
  -- 限定只检查三个目标字段
  AND key IN ('r_dirigeant', 'r_actionnaire', 'r_beneficiaire')
  -- 筛选字段值为oui的键
  AND value = 'oui';

运行上述代码得到的结果为{r_actionnaire}，和你预期的输出一致。

方式2：直接使用jsonpath查询 #

如果你偏好使用jsonpath实现，可以用如下表达式：

SELECT jsonb_path_query(
  json_arr, 
  '$[*] ? (
    @.r_ppe == "oui" 
    && (@.r_dirigeant == "oui" || @.r_actionnaire == "oui" || @.r_beneficiaire == "oui")
  ).keyvalue() ? (
    @.value == "oui" 
    && (@.key == "r_dirigeant" || @.key == "r_actionnaire" || @.key == "r_beneficiaire")
  ).key'
) FROM sample_data;

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