How to select all nested values at a particular level in a json structure in a JSONB type column in postgresql?

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 only the values of the b2 property from all rows of Column2. If it has just one row, then just '1231342138adc2fehj3j21321321321kjk1423j32k9'. If it has multiple rows, any value that it finds inside each jsonb inside each row.

Any help is greatly appreciated.

I tried:

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

SELECT * returns the whole row of the table but I don't know how to instruct it to return just whatever value the b2 property contains (nested inside the json from the row) .

Answer 1

It seems you only have a single object in each b array. Therefore you odn't actually need a path query, you can use normal JSON accessors.

-> retrieves a value as json(b) and ->> retrieves it as text

SELECT t.jsonColumn->'b'->0->>'b2'
FROM t
WHERE t.jsonColumn->'b'->0->>'b2' IN ('Value1', 'Value2');

db<>fiddle