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 example (
id serial not null constraint example_pk primary key,
data json not null);
INSERT INTO public.example (id, data) VALUES
(1, '[{"key": "1", "value": "val11"}, {"key": "2", "value": "val12"}]')
, (2, '[{"key": "1", "value": "val21"}]')
, (3, '[{"key": "1", "value": "val31"}, {"key": "2", "value": "val32"}]')
, (4, '[{"key": "2", "value": "val41"}, {"key": "2", "value": "val42"}]')
returning *;
CREATE TABLE
| id | data |
|---|---|
| 1 | [{"key": "1", "value": "val11"}, {"key": "2", "value": "val12"}] |
| 2 | [{"key": "1", "value": "val21"}] |
| 3 | [{"key": "1", "value": "val31"}, {"key": "2", "value": "val32"}] |
| 4 | [{"key": "2", "value": "val41"}, {"key": "2", "value": "val42"}] |
INSERT 0 4
--row 2 missing
SELECT id,
jsonb_path_query(
data::jsonb,
'$[*] ? (@.key == "2").value'
)::text AS values
FROM example;
| id | values |
|---|---|
| 1 | "val12" |
| 3 | "val32" |
| 4 | "val41" |
| 4 | "val42" |
SELECT 4
--equivalent of the above
SELECT id, values
FROM example, lateral jsonb_path_query(
data::jsonb,
'$[*] ? (@.key == "2").value'
) AS v(values);
| id | values |
|---|---|
| 1 | "val12" |
| 3 | "val32" |
| 4 | "val41" |
| 4 | "val42" |
SELECT 4
--again, an equivalent, but using an explicit join
SELECT id,
values
FROM example join
jsonb_path_query(data::jsonb,
'$[*] ? (@.key == "2").value'
) AS v(values)
on true;
| id | values |
|---|---|
| 1 | "val12" |
| 3 | "val32" |
| 4 | "val41" |
| 4 | "val42" |
SELECT 4
--solved with a scalar function
SELECT id, jsonb_path_query_first(data::jsonb, '$[*]?(@.key=="2").value')
FROM example;
| id | jsonb_path_query_first |
|---|---|
| 1 | "val12" |
| 2 | null |
| 3 | "val32" |
| 4 | "val41" |
SELECT 4
--solved with a scalar subquery
SELECT id,(SELECT jsonb_path_query(data::jsonb,'$[*]?(@.key=="2").value')LIMIT 1)
FROM example;
| id | jsonb_path_query |
|---|---|
| 1 | "val12" |
| 2 | null |
| 3 | "val32" |
| 4 | "val41" |
SELECT 4
--solved with a more appropriate explicit join
SELECT id, values
FROM example LEFT JOIN jsonb_path_query(data::jsonb, '$[*]?(@.key=="2").value')
AS srf(values)
ON true;
| id | values |
|---|---|
| 1 | "val12" |
| 2 | null |
| 3 | "val32" |
| 4 | "val41" |
| 4 | "val42" |
SELECT 5