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 configuration_matrix (cmt_json_value json);

CREATE TABLE

insert into configuration_matrix values ('[{"name": "Pending", "value": "PENDING"}, {"name": "Error", "value": "ERROR"},{"name":"Complete", "value":"COMPLETE"},{"name":"In-Progress", "value":"IN_PROGRESS"}]')

INSERT 0 1

select
      j->>'name', j->>'value'
from configuration_matrix cm
cross join json_array_elements(cm.cmt_json_value) j

?column?
?column?

Pending
PENDING

Error
ERROR

Complete
COMPLETE

In-Progress
IN_PROGRESS

SELECT 4

select (
    select
      json_object_agg((j->>'name'), (j->>'value'))
    from json_array_elements(cm.cmt_json_value) j
)
from configuration_matrix cm;

json_object_agg

{ "Pending" : "PENDING", "Error" : "ERROR", "Complete" : "COMPLETE", "In-Progress" : "IN_PROGRESS" }

SELECT 1

?column?	?column?
Pending	PENDING
Error	ERROR
Complete	COMPLETE
In-Progress	IN_PROGRESS