Using macOS, I haven’t seen a system-wide way to change the sound of notifications on a per app basis. I believe it needs to be made as a feature in the desktop application.
I already use Monterey’s focus mode, but it’d be really nice to distinguish sounds coming off of Obsidian’s day planning plugins (e.g. Day Planner) vs e.g. a Firefox notif. about a chat message in Google Chat or Teams.