json - 通过其他键和值在 JSON 中查找键和值

Question

在 PostgreSQL 中，我试图获取密钥 (r_dirigeant, r_actionnaire, r_beneficiaire) 或在 "r_ppe" = "oui" 并且其中一个密钥 (r_dirigeant, r_actionnaire, r_beneficiaire) 也是 = 'oui 时获取包含所有密钥的文本'。我试过这样的jsonpath $[?("r_ppe"="oui")]，但我想不通。

这是我正在使用的 JSON，例如：

[
  {
    "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
  }
]

在这个例子中，我想要这样的文本 ('r_actionnaire')

标签： jsonpostgresqljsonpath

Answer 1

select 
(
  select string_agg(key, ',')
  from jsonb_each_text(j) 
  where key in ('r_dirigeant','r_actionnaire','r_beneficiaire') 
  and value='oui'
) 
from jsonb_array_elements
($JSON$[
  {
    "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": "oui",
    "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
  }
]$JSON$) j
where j->>'r_ppe'='oui'
and 'oui' in (j->>'r_dirigeant',j->>'r_actionnaire',j->>'r_beneficiaire');