#json #postgresql #aggregate-functions
#json #postgresql #агрегатные функции
Вопрос:
У меня возникла необходимость создать иерархический объект JSON из результата запроса SQL.
Поскольку у меня возникли трудности с этим, я смоделировал этот набор данных:
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
)
Как вы можете создать объект JSON из этого со структурой:
{
"artist's name" : {
"album's name" : {
"1" : "title track 1",
"2" : "title track 2",
…
"n" : "title track n"
},
"next album" : { … },
…
},
"next artist" : { … },
…
}
Как бы вы это решили?
Ответ №1:
Это мой взгляд на проблему:
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
Во внутреннем выборе (АЛЬБОМЫ) я объединяю все номера треков и названия треков. Таким образом, номер трека становится ключом, а заголовок — значением. Это делается для каждого исполнителя и альбома.
Для примера данных это даст мне 3 строки, по одной для каждого альбома.
затем next select (ДИСКОГРАФИЯ) объединяет все альбомы исполнителя в один объект. Таким образом, останется 2 строки. По одному для каждого исполнителя.
Окончательный, самый удаленный выбор даст мне предполагаемую структуру.
С помощью CTE это может быть записано как:
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