Finding key and value in JSON by other key and value

user_16659605 0
Viewed 38

In PostgreSQL I'm trying to get the key (r_dirigeant, r_actionnaire, r_beneficiaire) or get a text with all the keys when "r_ppe" = "oui" and one of the keys (r_dirigeant, r_actionnaire, r_beneficiaire) is also = 'oui'. I tried jsonpath like this $[?("r_ppe"="oui")], but I can't figure it out.

This is the JSON I'm working with for example:

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

In this example I want a text like this ('r_actionnaire')

jsonpath postgresql json
2 Answers
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');
user_23020320

If you want to use jsonb_path_query, you can get the values like that

select j->'r_dirigeant',j->'r_actionnaire',j->'r_beneficiaire' from  
jsonb_path_query('[
  {
    "r_id": "00000000",
    "r_ppe": "oui",
    "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,'$[*] ? (@.r_ppe=="oui" && (@.r_dirigeant=="oui" || @.r_actionnaire=="oui" || @.r_beneficiaire=="oui"))') j
user_148710180
Related