I've found an issue introduced in iOS 17. Apps that have notifications in CarPlay (Message, Whasta, Telegram, etc...) can play sounds without the need of displaying any notification alert. If you turn "Show in CarPlay" to off, which is the default value for all other apps, sound will not play unless you check at least one alert type.