Search code examples
postgresqljsonb

Looking for a value in a jsonb list of keys/values


I have a postgresql table of cities (1 row = 1 city) with a jsonb colum containing the name of the city in different languages (as a list, not an array). For example for Paris(France) I have:

id_city (integer)  = 7444
name_city (text)   = Paris
names_i18n (jsonb) = {"name:fr":"Paris","name:zh":"巴黎","name:it":"Parigi",...}

In reality in my table I have around 20 different languages. So I try to find a city looking for any name:xx's value that could match a parameter given by the user, but I can't find how to query the jsonb column in that way. I've tried something like the request below but it doesn't seem to be the good syntaxe

select * from jsonb_each_text(select names_i18n from CityTable)
where value ilike 'Parigi'

I have also tried the following

select * from CityTable where names_i18n ? 'Parigi';

But it seems to work only for the key part of the jsonb, is there any similar operator for the value part? I also need a way to know what name:XX has been found, not only the city name. Anyone has a clue?


Solution

  • with CityTable (id_city, name_city, names_i18n) as (values(
        7444, 'Paris',
        '{"name:fr":"Paris","name:zh":"巴黎","name:it":"Parigi"}'::jsonb
    ))
    select *
    from CityTable, jsonb_each_text(names_i18n) jbet (key, value)
    where value ilike 'Parigi'
    ;
     id_city | name_city |                          names_i18n                          |   key   | value  
    ---------+-----------+--------------------------------------------------------------+---------+--------
        7444 | Paris     | {"name:fr": "Paris", "name:it": "Parigi", "name:zh": "巴黎"} | name:it | Parigi