I have a column as jsonb in PostgreSQL which can have the below json array references:
[
{
"endTime": {
"dayOfWeek": "TUESDAY",
"timeOfDay": {
"hours": 14,
"minutes": 30
}
},
"skillCode": "1000124",
"startTime": {
"dayOfWeek": "TUESDAY",
"timeOfDay": {
"hours": 9,
"minutes": 0
}
},
"premiumBreak": {
"hours": 0,
"minutes": 0
},
"standardBreak": {
"hours": 0,
"minutes": 30
}
},
{
"endTime": {
"dayOfWeek": "WEDNESDAY",
"timeOfDay": {
"hours": 14,
"minutes": 30
}
},
"skillCode": "1000176",
"startTime": {
"dayOfWeek": "WEDNESDAY",
"timeOfDay": {
"hours": 9,
"minutes": 0
}
},
"premiumBreak": {
"hours": 0,
"minutes": 0
},
"standardBreak": {
"hours": 0,
"minutes": 30
}
},
{
"endTime": {
"dayOfWeek": "THURSDAY",
"timeOfDay": {
"hours": 14,
"minutes": 30
}
},
"skillCode": "1000176",
"startTime": {
"dayOfWeek": "THURSDAY",
"timeOfDay": {
"hours": 9,
"minutes": 0
}
},
"premiumBreak": {
"hours": 0,
"minutes": 0
},
"standardBreak": {
"hours": 0,
"minutes": 30
}
}
]
I have 10k+ records and have to fetch records based on the below criteria :
Records which has this array objects which has-> Same "endTime", "startTime" but different "skillCode".
How to write a query to get lists of all records having the above criteria.
What I have done :
- I am able to get records based on the json object array column based on one key.
SELECT * FROM table t WHERE EXISTS ( SELECT * FROM jsonb_array_elements(t.column) AS jsond WHERE (jsond ->> 'skillCode')::int = 1000128);
- But not able to get how to compare the objects among themselves like we put over a loop and check-in Java code.