2

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.

Ragnar Lothbrok
  • 306
  • 1
  • 4
  • 22

1 Answers1

1

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);
Tarazed
  • 1,707
  • 1
  • 7
  • 22
  • This is outputting 74 – Ragnar Lothbrok Apr 02 '20 at 21:29
  • Hmm, looking at your results, it looks like array may be returned as a string rather than an array object. So it may be counting the string characters. We may have to do some string manipulation. Let me edit. – Tarazed Apr 02 '20 at 21:35
  • 1
    Technically you don't have to do the slice because all you are doing is counting and the split would end the same anyway. But I included it in case you needed to access the individual numbers as an array, it removes the [ and ]. – Tarazed Apr 02 '20 at 21:52