Postgres 11

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 tbl AS (SELECT jsonb '[{"a":"1","b":"2"},{"c":"3","d":"4","e":""}]' AS j);
1 rows affected

CREATE OR REPLACE FUNCTION f_parse(_in jsonb)
  RETURNS text LANGUAGE sql STABLE AS
$func$
SELECT concat('{{', string_agg(part, '},{'), '}}')
FROM   jsonb_array_elements(_in) q1
CROSS  JOIN LATERAL (
   SELECT string_agg(concat(q2.key, '=', q2.value::text), ',') AS part
   FROM   jsonb_each(q1.value) q2
   ) q2
$func$;
CREATE OR REPLACE FUNCTION f_parse_safe(_in jsonb)
  RETURNS text LANGUAGE sql STABLE AS
$func$
SELECT concat('{{', string_agg(part, '},{' ORDER BY q1.ordinality), '}}')
FROM   jsonb_array_elements(_in) WITH ORDINALITY q1
CROSS  JOIN LATERAL (
   SELECT string_agg(concat(q2.key, '=', q2.value::text), ',' ORDER BY q2.ordinality) AS part
   FROM   jsonb_each(q1.value) WITH ORDINALITY q2
   ) q2
$func$;
SELECT j::text FROM tbl
UNION ALL
SELECT '{' || regexp_replace( left(right(j::text, -1), -1)
                            , '"([^"]+)": *', '\1=', 'g')
    || '}'
FROM  tbl
UNION ALL SELECT f_parse(j) FROM tbl
UNION ALL SELECT f_parse_safe(j) FROM tbl;


j


[{"a": "1", "b": "2"}, {"c": "3", "d": "4", "e": ""}]

{{a="1", b="2"}, {c="3", d="4", e=""}}

{{a="1",b="2"},{c="3",d="4",e=""}}

{{a="1",b="2"},{c="3",d="4",e=""}}