[SOLVED] Count amount of arrays in a database

Count amount of arrays in a database

I have a database and I will lay out an example.

guildid    botid
1234       ["2345","3456","3714","1812"]
9876       ["8765","5432"]
4346       NULL
2371       ["3179"]
1254       NULL
1257       ["1721","7104","8265"]
8321       NULL

I need to output the amount of botids there are, in this example the answer = 10. There are 7 records in the db, but 10 total ids that are not NULL Normally I would do SELECT count(*) FROM watchedbots However this only counts how many records there are. On some occasions, the botid can also be NULL so I need it to ignore the NULL entries. I am fairly new when it comes to arrays so any help would be appreciated. The module I use for SQL is better-sqlite3

Edit: expanded the example

If I do the following:

const dataGrab = db.prepare('SELECT botid FROM watchedbots').all()
const stringify = JSON.stringify(dataGrab)
console.log(stringify)

I get an output of: [{"botid":null},{"botid":null},{"botid":null},{"botid":"["1721","7104","8265"]"},{"botid":"["2345","3456","3714","1812"]"},{"botid":"["3179"]"},{"botid":"["8765","5432"]"}] Which I am guessing is a good next step, not sure where I would go from there though.

Solution

I think your simplest method is to iterate over the base array.

let count = 0;
for(guild of dataGrab) {
    if(guild.botid) {
        let arr = guild.botid.slice(1, guild.botid.length -1).split(',');
        count += arr.length;
    }
}
console.log(count);