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?.
WITH settings (program_id, client_id, filters) AS (
VALUES (1, 1, jsonb '{
"sources": [
{
"conditions": [
{
"value": [
{
"id": 1211,
"name": "ABM BETA INVITE LIST",
"isSelected": true
}
],
"condition": "one of the following"
}
],
"objectType": "SmartLists",
"subscriptionId": 1173,
"integrationType": "mkto"
}
],
"listType": "All Accounts",
"programId": 30203,
"noOfAccounts": null,
"expiryDuration": 0,
"subscriptionId": null,
"updateFrequency": null
}')
)
SELECT *
FROM settings
WHERE filters #>> '{sources, 0, integrationType}' = 'mkto';
| program_id | client_id | filters |
|---|---|---|
| 1 | 1 | {"sources": [{"conditions": [{"value": [{"id": 1211, "name": "ABM BETA INVITE LIST", "isSelected": true}], "condition": "one of the following"}], "objectType": "SmartLists", "subscriptionId": 1173, "integrationType": "mkto"}], "listType": "All Accounts", "programId": 30203, "noOfAccounts": null, "expiryDuration": 0, "subscriptionId": null, "updateFrequency": null} |
SELECT 1