Concatenating Nested JSONB Arrays into a Single String

Question

In a Postgres 11 database, there's a table (traces) with one column of type JSONB (trace). The JSON value is always a nested array in the form of:

[ ["a", "b"], ... ]

There's at least one sub-element in the array in each row. I want to add a second column (computed, but for now a simple query suffices for this scenario), which contains a string representation of the array column in the form of

a.b c.d.e

from an array value of [["a","b"],["c","d","e"]].

I have tried several things, but I might be missing a bit a theory here. In my mind, this would involve some kind of double-aggregation, once for each nested array, then again for the outermost array. How do I express that in a query (if that is even the right approach)?

My starting point was this query to first get access to all nested arrays:

SELECT nested FROM traces, jsonb_array_elements(trace) nested;

It does return a list of nested arrays, with nested being JSONB I think. I continued with approaches like this:

SELECT
       trace,
       array_to_string(array_agg(nested), ' ')
FROM traces,
     jsonb_array_elements(trace) nested
GROUP BY trace;

But I ran into the issue of not being able to "nest" aggregation functions.

S-Man · Accepted Answer · 2019-09-01 07:36:04Z

demo:db<>fiddle

SELECT
    trace,
    string_agg(point_separated, ' ')                             -- 4
FROM (
    SELECT
        trace,
        string_agg(second_level, '.') AS point_separated         -- 3
    FROM
        traces,
        jsonb_array_elements(trace) as first_level,              -- 1
        jsonb_array_elements_text(first_level) as second_level   -- 2
    GROUP BY trace, first_level.value
) s
GROUP BY trace

Expand the nested arrays into one record per nested array with jsonb_array_elements()
Expand the elements of the nested arrays into one record per element with a second call of this function.

Intermediate result so far:

trace                         | value           | value
:---------------------------- | :-------------- | :----
[["a", "b"], ["c", "d", "e"]] | ["a", "b"]      | a    
[["a", "b"], ["c", "d", "e"]] | ["a", "b"]      | b    
[["a", "b"], ["c", "d", "e"]] | ["c", "d", "e"] | c    
[["a", "b"], ["c", "d", "e"]] | ["c", "d", "e"] | d    
[["a", "b"], ["c", "d", "e"]] | ["c", "d", "e"] | e    
[["e", "f", "g"], ["h", "i"]] | ["e", "f", "g"] | e    
[["e", "f", "g"], ["h", "i"]] | ["e", "f", "g"] | f    
[["e", "f", "g"], ["h", "i"]] | ["e", "f", "g"] | g    
[["e", "f", "g"], ["h", "i"]] | ["h", "i"]      | h    
[["e", "f", "g"], ["h", "i"]] | ["h", "i"]      | i

Aggregate the inner elements into a point-separated string using GROUP BY and string_agg()
Aggregate these results into a space-separated string using a second call of the this.

If the order of the aggregated strings is important to you, you need to add a row count because aggregations like string_agg() do not guarantee a certain order if you do not tell them.

Set-returning functions like jsonb_array_elements() support the WITH ORDINALITY extension which adds such a row number. This can be used to add the ORDER BY into the string_agg() function:

demo:db<>fiddle

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

Thank you, that's exactly the solution I arrived at yesterday! I did have to use WITH ORDINALITY to preserve array order.

Collectives™ on Stack Overflow

Concatenating Nested JSONB Arrays into a Single String

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related