0

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.

1 Answer 1

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.

2
  • Thanks a lot! This works! Could you tell me how to look for 2 or more values as well instead of just one, please? Sep 9, 2022 at 7:51
  • 1
    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 Sep 9, 2022 at 8:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.