nested aggregation in postgres

Question

I had the need to create a hierarchical JSON object from an SQL query result.

As I had difficulties getting my head around this, I mocked up this set of data:

WITH music AS (
    SELECT
        'Abba' AS artist, 
            'Ring Ring' AS album,
                1 AS trackno, 'Ring Ring' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Ring Ring' AS album,
                2 AS trackno, 'Another Town, Another Train' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Ring Ring' AS album,
                3 AS trackno, 'Disillusion' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Ring Ring' AS album,
                4 AS trackno, 'People Need Love' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Ring Ring' AS album,
                5 AS trackno, 'I Saw It in the Mirror' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Ring Ring' AS album,
                6 AS trackno, 'Nina, Pretty Ballerina' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Ring Ring' AS album,
                7 AS trackno, 'Love Isn´t Easy (But It Sure Is Hard Enough)' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Ring Ring' AS album,
                8 AS trackno, 'Me and Bobby and Bobby´s Brother' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Ring Ring' AS album,
                9 AS trackno, 'He Is Your Brother' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Ring Ring' AS album,
                10 AS trackno, 'She´s My Kind of Girl' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Ring Ring' AS album,
                11 AS trackno, 'I Am Just a Girl' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Ring Ring' AS album,
                12 AS trackno, 'Rock´n Roll Band' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Waterloo' AS album,
                1 AS trackno, 'Waterloo' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Waterloo' AS album,
                2 AS trackno, 'Sitting in the Palmtree' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Waterloo' AS album,
                3 AS trackno, 'King Kong Song' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Waterloo' AS album,
                4 AS trackno, 'Hasta Mañana' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Waterloo' AS album,
                5 AS trackno, 'My Mama Said' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Waterloo' AS album,
                6 AS trackno, 'Dance (While the Music Still Goes On)' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Waterloo' AS album,
                7 AS trackno, 'Honey, Honey' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Waterloo' AS album,
                8 AS trackno, 'Watch Out' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Waterloo' AS album,
                9 AS trackno, 'What About Livingstone?' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Waterloo' AS album,
                10 AS trackno, 'Gonna Sing You My Lovesong' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Waterloo' AS album,
                11 AS trackno, 'Suzy-Hang-Around' AS tracktitle
    UNION ALL
    SELECT
        'Abba' AS artist, 
            'Waterloo' AS album,
                12 AS trackno, 'Waterloo' AS tracktitle
    UNION ALL
    SELECT
        'The Beatles' AS artist, 
            'Please Please Me' AS album,
                1 AS trackno, 'I Saw Her Standing There' AS tracktitle
    UNION ALL
    SELECT
        'The Beatles' AS artist, 
            'Please Please Me' AS album,
                2 AS trackno, 'Misery' AS tracktitle
    UNION ALL
    SELECT
        'The Beatles' AS artist, 
            'Please Please Me' AS album,
                3 AS trackno, 'Anna (Go to Him)' AS tracktitle
    UNION ALL
    SELECT
        'The Beatles' AS artist, 
            'Please Please Me' AS album,
                4 AS trackno, 'Chains' AS tracktitle
    UNION ALL
    SELECT
        'The Beatles' AS artist, 
            'Please Please Me' AS album,
                5 AS trackno, 'Boys' AS tracktitle
    UNION ALL
    SELECT
        'The Beatles' AS artist, 
            'Please Please Me' AS album,
                6 AS trackno, 'Ask Me Why' AS tracktitle
    UNION ALL
    SELECT
        'The Beatles' AS artist, 
            'Please Please Me' AS album,
                7 AS trackno, 'Please Please Me' AS tracktitle
    UNION ALL
    SELECT
        'The Beatles' AS artist, 
            'Please Please Me' AS album,
                8 AS trackno, 'Love Me Do' AS tracktitle
    UNION ALL
    SELECT
        'The Beatles' AS artist, 
            'Please Please Me' AS album,
                9 AS trackno, 'P.S. I Love You' AS tracktitle
    UNION ALL
    SELECT
        'The Beatles' AS artist, 
            'Please Please Me' AS album,
                10 AS trackno, 'Baby It´s You' AS tracktitle
    UNION ALL
    SELECT
        'The Beatles' AS artist, 
            'Please Please Me' AS album,
                11 AS trackno, 'Do You Want to Know a Secret' AS tracktitle
    UNION ALL
    SELECT
        'The Beatles' AS artist, 
            'Please Please Me' AS album,
                12 AS trackno, 'A Taste of Honey' AS tracktitle
    UNION ALL
    SELECT
        'The Beatles' AS artist, 
            'Please Please Me' AS album,
                13 AS trackno, 'There´s a Place' AS tracktitle
    UNION ALL
    SELECT
        'The Beatles' AS artist,
              'Please Please Me' AS album,
                      14 AS trackno, 'Twist and Shout' AS tracktitle
)

How can you create a JSON object from this with the structure:

{
  "artist's name" : {
    "album's name" : {
      "1" : "title track 1",
      "2" : "title track 2",
      …
      "n" : "title track n"
    },
    "next album" : { … },
    …
  },
  "next artist" : { … },
  …
}

How would you solve this?

Skeeve · Accepted Answer · 2020-09-18 14:53:49Z

This is m,y take on the issue:

SELECT JSON_OBJECT_AGG( artist, discography )
FROM (
        SELECT artist, JSON_OBJECT_AGG( album, tracks ) AS discography
        FROM (
                SELECT artist, album, JSON_OBJECT_AGG(trackno, tracktitle) as tracks
                FROM music
                GROUP BY artist, album
        ) ALBUMS
        GROUP BY artist
) DISCOGRAPHY

In the inner select (ALBUMS), I aggregate all tracknumers and tracktitles. So the tracknumber becomes the key and the title becomes the value. This is done per artist and album.

For the example data this will give me 3 rows, one for each album.

then next select (DISCOGRAPHY) aggregates all albums of an artist into one object. So 2 rows will remain. One for each artist.

The final, the outermost select will then give me the intended structure.

With CTEs this can be written as:

ALBUMS AS (
    SELECT artist, album, JSON_OBJECT_AGG(trackno, tracktitle) as tracks
    FROM music
    GROUP BY artist, album
),
DISCOGRAPHY AS (
    SELECT artist, JSON_OBJECT_AGG( album, tracks ) AS discography
    FROM ALBUMS
    GROUP BY artist
)
SELECT JSON_OBJECT_AGG( artist, discography )
FROM DISCOGRAPHY

Collectives™ on Stack Overflow

nested aggregation in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related