How to select all nested values at a particular level in a json structure in a JSONB type column in postgresql?
0
votes
1
answer
4170
views
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) .
Asked by JustNatural
(105 rep)
Sep 9, 2022, 08:46 AM
Last activity: Sep 9, 2022, 10:16 AM
Last activity: Sep 9, 2022, 10:16 AM