I think @rackandboneman and the others did a great job the "why and when it is desirable" part of the questions. But the "technically what garlic and onion is adding to the flavour" has triggered my scientific curiosity, and I would like to share another perspective on that bit!
Onion and garlic are both members of the Allium family (together with many great ingredients like leeks, spring onions, chives, etc). What all members of this family have in common is the presence of sulfur-rich compounds. Loosely speaking, compounds containing sulfur are usually very smelly. Think about odorized natural gas, rotten eggs, match heads, vulcano smoke (?).
There are many sulfur-containing compounds in garlic. But alliin is probably the most distinctive one. Chopping the garlic triggers a reaction that converts alliin into allicin, responsible for the characteristic smell of garlic. Here lies one culinary lesson: if you want to optimise the strong raw garlic flavour, chop it and leave it to fully react before using in your preparation.
On the other hand, instead of alliin, onions contain isoallin, a compound with the same chemical formula but different bond structure. Again, when the onion is chopped a chain of reactions is triggered. What is very curious is that the subtle chemical. The distinctive subproduct of isoalliin decomposition is a volatile compound propanethial S-oxide (PSO) which is responsible for the crying effect in onions. Again, another lesson: the longer you leave the chopped onions standing, the stronger the crying power.
I find this story interesting but offers only an explanation of the difference between raw garlic and onion. Allicin and PSO are both unstable when cooked under heat. You still get notes of raw garlic in fried or baked garlic, and to a less extent from onions, but it is not the whole picture.
The first important process that happens when cooking has not much to do with sulfur. It is the Maillard reaction, which in short is the conversion of sugars into flavourful molecules that give the 'roasted', 'umami' and 'caramelized' feel typical from bread crust or pan-fried meat. Onions typically have 4% of their weight of sugars, and garlic about 1%. However, since onion is usually used in much more quantity than garlic in recipes, it is clearly a dominant source of sugar. But there are exceptions. Different from what one might expect, roasted garlic soup or garlic purée is not that pungent and has a surprisingly sweet taste.
Apart from Maillard reaction, it seems that PSO decomposes into 3-mercapto-2-methylpentan-1-ol (MMP) under slow and long cooking, a compound that tastes like meat broth.. Since garlic does not have isoalliin, MMP is not produced when garlic is cooked. Maybe this is one of the reasons why onion pairs better with meat ragù than garlic? Mystery!
I found this and this useful references when reading about garlic/onions chemistry!