How do I query inside a JSONB column when all values have the same json-like structure?

Question

In a postgresql database, I have a table with 2 columns:

Column1 is of type VARCHAR and contains multiple rows of ids in this format: ID1123312

Column2 is of type JSONB and contains multiple rows of jsons which have this structure:

{
 "a": [
 {
 "a1": "2022年01月01日",
 "a2": "2026年01月31日",
 "a3": 1
 }
 ],
 "b": [
 {
 "b1": "J",
 "b2": "1231342138adc2fehj3j21321321321kjk1423j32k9"
 }
 ],
 "c": [
 {
 "c1-1": "2021年02月01日",
 "c1-2": "2021年01月01日"
 },
 {
 "c2-1": "2021年04月01日",
 "c2-2": "2021年03月01日"
 }
 ]
}

I need to build a SELECT statement to return the row above giving the value of b2 (1231342138adc2fehj3j21321321321kjk1423j32k9) as an input parameter.

Something like:

SELECT * 
FROM table 
WHERE [Column2 contains b which contains b2 which has value '1231342138adc2fehj3j21321321321kjk1423j32k9'] ; 

OR just:

SELECT * 
FROM table 
WHERE [value '1231342138adc2fehj3j21321321321kjk1423j32k9' exists in row from Column2 ]

I just don't know how to give this instruction.

Any help is greatly appreciated.

Answer 1

You can use jsonb_path_exists along with a JSON path query

SELECT *
FROM t
WHERE jsonb_path_exists(t.jsonColumn, '$.b[*].b2 ? (@ == "1231342138adc2fehj3j21321321321kjk1423j32k9")')

db<>fiddle

The [*] in this path means "any array element" and ? (@ == ...) mean a predicate that the value at that path must equal your value.

• JustNatural: Thanks a lot! This works! Could you tell me how to look for 2 or more values as well instead of just one, please?
• Charlieface: Depends on your exact requirements. Do you want to look for one of two b2 values, or are you looking for different properties? I suggest you take a good read of the documentation postgresql.org/docs/12/… and create a new question if you don't understand it