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",
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"] |