Postgres 14

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 it220208 (
  js json
);

insert into it220208 values
('[{"Date":"2022/1/1","Light":"blue"},{"Date":"2022/1/15","Light":"red"},{"Date":"2022/1/20","Light":"black"},{"Date":"2022/1/26","Light":"blue"}]'),
('[{"Date":"2022/2/3","Light":"blue"},{"Date":"2022/2/11","Light":"blue"},{"Date":"2022/2/16","Light":"black"},{"Date":"2022/2/18","Light":"red"},{"Date":"2022/2/22","Light":"black"}]');


CREATE TABLE



INSERT 0 2


select jsonb_pretty(js::jsonb)
  from it220208;


jsonb_pretty


[
    {
        "Date": "2022/1/1",
        "Light": "blue"
    },
    {
        "Date": "2022/1/15",
        "Light": "red"
    },
    {
        "Date": "2022/1/20",
        "Light": "black"
    },
    {
        "Date": "2022/1/26",
        "Light": "blue"
    }
]

[
    {
        "Date": "2022/2/3",
        "Light": "blue"
    },
    {
        "Date": "2022/2/11",
        "Light": "blue"
    },
    {
        "Date": "2022/2/16",
        "Light": "black"
    },
    {
        "Date": "2022/2/18",
        "Light": "red"
    },
    {
        "Date": "2022/2/22",
        "Light": "black"
    }
]



SELECT 2


with t1 as (
select json_array_elements(js) as injs
  from it220208
)
select injs->>'Date' as black_dt
  from t1
 where injs->>'Light' = 'black';


black_dt


2022/1/20

2022/2/16

2022/2/22



SELECT 3