CODEWITHSUNDEEP
postgresqljsonb
MrJibus
MrJibus

Reputation: 113

Deep search within jsonb field PostgreSQL without knowking the key

Assuming the following jsonb data :

{
    "3f210b": {
        "hash": "e60ab24c549ac7060fzedfefe563e8493d759bb"
    },
    "Siaa1b3": {
        "hash": "d24b37efgregr1a2c6db3b9334b3bf4fef3f22bfc9a43f"
    }
}

Is it possible, in PostgreSQL, to do a deep jsonb search without know the key (3f210b & Siaa1b3) but we know the hash.

Upvotes: 0

Views: 301

Answers (1)

user330315
user330315

Reputation:

With Postgres 11 you will need to unnest that value:

select *
from the_table t
where exists (select *
              from jsonb_each(t.the_column) as x(ky, item)
              where x.item ->> 'hash' = 'd24b37efgregr1a2c6db3b9334b3bf4fef3f22bfc9a43f');

With Postgres 12, you could use the new JSON/Path expression:

select *
from the_table t
where jsonb_path_exists(the_column, '$.*.hash ? (@ == "d24b37efgregr1a2c6db3b9334b3bf4fef3f22bfc9a43f")')

Upvotes: 3

Related Questions