By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE payment_records (
id int NOT NULL PRIMARY KEY
, record jsonb NOT NULL
);
INSERT INTO payment_records values
(1, '{
"records": [{
"recordId": "5f5d8e39-1b57-4a05-9bdd-cb5e957768fc",
"payment": {
"booked": true,
"errors": [],
"return": null,
"status": "Processed",
"latestStatusChangedTimestamp": "2024-10-30T07:43:40.5+00:00"
},
"timestamp": "2024-10-30T07:43:50.557939Z"
}, {
"recordId": "c3e049e8-a3e3-4e80-be63-bdf58e121c8a",
"payment": {
"booked": true,
"errors": [],
"return": null,
"status": "Processed",
"latestStatusChangedTimestamp": "2024-10-30T07:43:40.5+00:00"
},
"timestamp": "2024-10-30T07:43:50.557941Z"
}, {
"recordId": "abf35109-7d5f-4ba3-9f6e-2c6d1a305134",
"payment": {
"booked": true,
"errors": [],
"return": null,
"status": "Processed",
"latestStatusChangedTimestamp": "2024-10-30T07:43:40.5+00:00"
},
CREATE TABLE
INSERT 0 2
SELECT *
FROM payment_records
WHERE record @> '{"records": [{"recordId":"abf35109-7d5f-4ba3-9f6e-2c6d1a305134"}]}';
| id | record |
|---|---|
| 1 | {"records": [{"payment": {"booked": true, "errors": [], "return": null, "status": "Processed", "latestStatusChangedTimestamp": "2024-10-30T07:43:40.5+00:00"}, "recordId": "5f5d8e39-1b57-4a05-9bdd-cb5e957768fc", "timestamp": "2024-10-30T07:43:50.557939Z"}, {"payment": {"booked": true, "errors": [], "return": null, "status": "Processed", "latestStatusChangedTimestamp": "2024-10-30T07:43:40.5+00:00"}, "recordId": "c3e049e8-a3e3-4e80-be63-bdf58e121c8a", "timestamp": "2024-10-30T07:43:50.557941Z"}, {"payment": {"booked": true, "errors": [], "return": null, "status": "Processed", "latestStatusChangedTimestamp": "2024-10-30T07:43:40.5+00:00"}, "recordId": "abf35109-7d5f-4ba3-9f6e-2c6d1a305134", "timestamp": "2024-10-30T07:43:50.5579416Z"}]} |
SELECT 1
SELECT *
FROM payment_records
WHERE record @? '$.records[*] ? (@."recordId" == "abf35109-7d5f-4ba3-9f6e-2c6d1a305134")';
| id | record |
|---|---|
| 1 | {"records": [{"payment": {"booked": true, "errors": [], "return": null, "status": "Processed", "latestStatusChangedTimestamp": "2024-10-30T07:43:40.5+00:00"}, "recordId": "5f5d8e39-1b57-4a05-9bdd-cb5e957768fc", "timestamp": "2024-10-30T07:43:50.557939Z"}, {"payment": {"booked": true, "errors": [], "return": null, "status": "Processed", "latestStatusChangedTimestamp": "2024-10-30T07:43:40.5+00:00"}, "recordId": "c3e049e8-a3e3-4e80-be63-bdf58e121c8a", "timestamp": "2024-10-30T07:43:50.557941Z"}, {"payment": {"booked": true, "errors": [], "return": null, "status": "Processed", "latestStatusChangedTimestamp": "2024-10-30T07:43:40.5+00:00"}, "recordId": "abf35109-7d5f-4ba3-9f6e-2c6d1a305134", "timestamp": "2024-10-30T07:43:50.5579416Z"}]} |
SELECT 1