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.