If my database table 'configurations' contains rows with a 'data' column in JSONB format like the example below:
{
"US": {
"1234": {
"id": "ABCD"
}
},
"CA": {
"5678": {
"id": "WXYZ"
}
}
}
I would like to write a query similar to this:
select * from configurations where data->'$.*.*.id' = 'WXYZ'
(Please note: The SQL statement above is not valid, it's pseudo code.)
Questions:
- What is the correct syntax for executing the query as mentioned?
- What type of index should be created to optimize performance and avoid scanning the entire table for such queries?