Is there a way to aggregate values in a column in sqlite in one-to-many relationship into array?

(1.2) By Kulagin on 2022-01-30 23:55:56 edited from 1.1 [link] [source]

For example, I have 2 tables like this:

Artists: ArtistId name 1 AC/DC 2 Accept

AlbumId ArtistId Title 1 1 For Those About To Rock We Salute You 2 1 Let There Be Rock 3 2 Balls to the Wall 4 2 Restless and Wild

When I just do a query with a join:

SELECT
   Name,
   Title
FROM
   artists
JOIN albums USING(ArtistId)
WHERE artists.ArtistId = 1;

I get:

alt-text

I found out that I can do group_concat:

sql
SELECT
   Name,
   GROUP_CONCAT(Title)
FROM
   artists
JOIN albums USING(ArtistId)
WHERE artists.ArtistId = 1;

To concatenate all values together:

alt-text

But I still have to parse the coma-separated string with titles: For Those About To Rock We Salute You,Let There Be Rock in the code to get the array of titles for each artist.

I use Python and I'd prefer to get something like a tuple for each row: (name, titlesArray).

A much easier way in this case for me would be to use json.loads and json.dumps functions to save all the array members into the same row, instead of using the recommended way for databases to save values in different tables and then use joins to retrieve them: the "many" values is an array on the object, and it's just much easier to save and get them using just 2 functions: json.loads and json.dumps, compared to manually saving the "many" values into a separate table, create binding to the "one" value, then use group_concat to concat them into a string, and then parse it more to actually get my array back.

Is it possible to get an array of values, or do I have to do group_concat and parse the string?

(2) By SeverKetor on 2022-01-31 00:41:57 in reply to 1.2 [link] [source]

If you must, then do

SELECT
   Name,
   json_group_array(Title) AS Titles
FROM
   artists
JOIN albums USING(ArtistId)
WHERE artists.ArtistId = 1;

and use json.loads on Titles. If you are actually just getting the list for single artists at a time, then you could just use that first statement you posted and a list comprehension.

(3) By Keith Medcalf (kmedcalf) on 2022-01-31 00:51:04 in reply to 1.2 [source]

You mean like this:

create table Artists
(
    ArtistID    integer primary key,
    Name        text collate nocase unique
);
create table Albums
(
    AlbumID     integer primary key,
    ArtistID    integer not null references Artists(ArtistID),
    Title       test collate nocase unique
);
create index AlbumArtistID on Albums (ArtistID);


insert into Artists values (1, 'AC/DC'),
                           (2, 'Accept');
insert into Albums values (1, 1, 'For Those About To Rock We Salute You'),
                          (2, 1, 'Let There Be Rock'),
                          (3, 2, 'Balls to the Wall'),
                          (4, 2, 'Restless and Wild');

as the definition of the database.db file contents and then the following Python code:

import pprint
import sqlite3

cn = sqlite3.connect('database.db', isolation_level=None)
cr = cn.cursor()
dictionary = {}
for row in cr.execute('select name, title from artists, albums where artists.artistid == albums.artistid'):
    if not row[0] in dictionary:
        dictionary[row[0]] = []
    dictionary[row[0]].append(row[1])
pprint.pprint(dictionary)

(4) By Donal Fellows (dkfellows) on 2022-02-09 15:20:13 in reply to 1.2 [link] [source]

Try using defaultdict(list) to collect the info out of the rows coming out of the DB instead of forcing the database to do complicated work that you then have to then undo yourself. That means you can just do:

result = defaultdict(list)
for row in cursor.execute("SELECT name, title FROM ..."): # Omitting the rest
    result[row["name"]].append(row["title"])

It doesn't get much easier than that.