Postgres 10

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 tab ( jsval json NOT NULL );

INSERT INTO tab VALUES 
('{"elements":[{"val":"value1", "column":"column1"}, 
               {"val":"value2", "column":"column2"},
               {"val":"value3", "column":"column3"}]}')
1 rows affected

   SELECT json_array_elements_text(v)::json->>'column' AS col,
          json_array_elements_text(v)::json->>'val' AS val
     FROM tab t
    CROSS JOIN json_each(jsval) as js(k,v)
    


col
val


column1
value1

column2
value2

column3
value3


SELECT MAX(val) FILTER (WHERE col = 'column1') as column1,
       MAX(val) FILTER (WHERE col = 'column2') as column2,
       MAX(val) FILTER (WHERE col = 'column3') as column3
  FROM 
  (
   SELECT json_array_elements_text(v)::json->>'column' AS col,
          json_array_elements_text(v)::json->>'val' AS val
     FROM tab t
    CROSS JOIN json_each(jsval) as js(k,v)
  ) q


column1
column2
column3


value1
value2
value3