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 traces (
    trace jsonb
);

INSERT INTO traces VALUES
('[["a","b"],["c","d","e"]]'),
('[["e","f","g"],["h","i"]]');

SELECT * FROM traces
2 rows affected



trace


[["a", "b"], ["c", "d", "e"]]

[["e", "f", "g"], ["h", "i"]]


    SELECT
        *
    FROM
        traces,
        jsonb_array_elements(trace) WITH ORDINALITY as first_level(val, number)


trace
val
number


[["a", "b"], ["c", "d", "e"]]
["a", "b"]
1

[["a", "b"], ["c", "d", "e"]]
["c", "d", "e"]
2

[["e", "f", "g"], ["h", "i"]]
["e", "f", "g"]
1

[["e", "f", "g"], ["h", "i"]]
["h", "i"]
2


SELECT
    trace,
    string_agg(point_separated, ' ' ORDER BY number)
FROM (
    SELECT
        trace,
        first_level.number,
        string_agg(second_level.val, '.' 
             ORDER BY first_level.number, second_level.number) AS point_separated
    FROM
        traces,
        jsonb_array_elements(trace) WITH ORDINALITY as first_level(val, number),
        jsonb_array_elements_text(first_level.val) WITH ORDINALITY as second_level(val, number)
    GROUP BY trace, first_level.val, first_level.number
) s
GROUP BY trace


trace
string_agg


[["a", "b"], ["c", "d", "e"]]
a.b c.d.e

[["e", "f", "g"], ["h", "i"]]
e.f.g h.i

trace	val	number
[["a", "b"], ["c", "d", "e"]]	["a", "b"]	1
[["a", "b"], ["c", "d", "e"]]	["c", "d", "e"]	2
[["e", "f", "g"], ["h", "i"]]	["e", "f", "g"]	1
[["e", "f", "g"], ["h", "i"]]	["h", "i"]	2

trace	string_agg
[["a", "b"], ["c", "d", "e"]]	a.b c.d.e
[["e", "f", "g"], ["h", "i"]]	e.f.g h.i