I wouldn’t look at spells above 1st level, maybe 2nd, unless we are talking a very high magic world.
Casters of the level needed to cast those higher level spells wouldn’t be common enough to lend to mass armies…and if they were it would make the army way too dependent on them. You take out one person and the army starves…that’s too great a risk.
I will say on the cantrip vs bow debate. I recently read several articles talking about the expense of arrows…limited ammunition is actually quite a big factor in the power of volley bows. But on the flip side how many people would realistically have attack cantrips