Humankind has this, and it feels much better than Civ5 and Civ6 in this regard
Even in Civ5 you can simply spam more units to replace the ones you lose, especially if you have high Production. This is especially noticeable in Civ6 where Production can easily get broken. In my last Civ6 game I went to war with Cree who had a whole bunch of cities with mines and industrial districts. I was killing 2-3 of their units every turn, but it had no effect no them because they could easily produce as many in as many turns. This is unrealistic because you can't you just keep spamming units just because you have mines and factories, there is no sense of the human cost (and the unhappiness that so large a loss in life should bring).
This is much better in Humankind; it forces me to pick and choose my battles instead of just mass-producing units and throwing them at my enemies when I know that the cost of replacing them is more than just a few extra turns.
Humankind though, allows you to work every tile around the city automatically, regardless of the city population. The population figures in that game (and in its predecessor,
Endless Legends) are more like Civ's Specialists than 'raw' population points in the way they act.
For a population limitation on Infinite Unit Spam, though, Specialists or any other sub-set of basic population can work as well as basic Population Points. IF, as in Humankind, the 'specialists' are the source of much of the city Production, Gold, Culture, etc., then losing them to unit production is as a major a cost as losing basic population.
I think a complete balancing of unit costs, production and limitations on spam will require adding several things not really in Civ 6 now:
1. Some direct relationship between population and units. Most simple would be 1 Unit = 1 population point, but removing Specialist points or reducing the effect of population on tiles ("partial population points" = units) although more complicated, would also work, and still allow small or starting cities to defend themselves.
2. Alternatives to your own population, in addition to the All or Nothing 'hire' City State armies. From at least the Bronze Age, foreign, barbarian, allied, neutral powers all contributed military strength to each other by hiring out soldiers and warriors as individuals, individual units, or entire armies. That flexibility needs to return (Barbarian Clans does include it now, with a limited ability to hire or buy Barbarian units from a Clan and even get Unique Units for extra Gold). This, connected to a limitation imposed by your own population numbers, would give a good approximation of the actual way armies worked until the Industrial Era. Frederick the Great famously said that the Prussian Army consisted of "1/3 native draftees, 1/3 hired mercenaries, and 1/3 foreign soldiers forcibly incorporated ("impressed")" into the army. That was, even for its time, excessive reliance on foreigners, but it might show the upper limit that a game could allow.
3. As posted, a mechanic to relate losses to country Morale, Happiness, and/or Loyalty. This could also be linked to type of government, since some governments are far more susceptible to negative reactions to heavy casualties while others are relatively impervious to them. And, lesser effects short of Revolt should also be included: when casualties are heavy, people willing to volunteer dry up rapidly, and even potential draftees become scarce on the ground: Napoleon by 1813, after almost 23 years of war, found that only a tiny fraction of the called up draftees actually showed up that year, and his army was about 1/3 under strength at Waterloo because almost none of trhe draftees called up for the campaign actually arrived. Correctly balanced with government type and country/Civ Morale, this would be a powerful Brake on the All-Conquering All The Time fantasy domination game.