Postgres 12

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 campaigns
(
  targeting jsonb
);
insert into campaigns 
values ('{
   "targets": [
     {
       "audienceSegments": [
         {
           "includes": [
             {
               "consumer": "selection",
               "segments": {
                 "allOf": [
                   {
                     "provider": "a",
                     "ids": [
                       {
                         "id": "110418"
                       },
                       {
                         "id": "110430"
                       },
                       {
                         "id": "110433"
                       }
                     ]
                   }
                 ]
               }
             }
           ],
           "excludes": [
             {
               "consumer": "selection",
               "segments": {
                 "allOf": [
                   {
                     "provider": "a",
                     "ids": [
                       {
                         "id": "109776"
                       }
                     ]
                   }
                 ]
               }
             }
           ]
         }
       ]
     }
   ]
 }');
1 rows affected

select jsonb_path_query_array(targeting, '$.targets[*].*.includes.**.ids.id')
from campaigns;


jsonb_path_query_array


["110418", "110430", "110433", "110418", "110430", "110433"]