The configuration of these things is compile-time static in st.
So, no, that's not possible with st (and the workaround to tell picom which window you don't want to have transparent would be pretty complicated). You're looking for a fully-featured terminal emulator, not the one whose declared goal is to keep all color-handling complexity out of the terminal emulator.
I'd honestly just go with alacritty (which is packaged for debian and fedora, so probably for most other Linux distros as well) instead of st. In your neovim startup scripts, you can just run the external command
alacritty msg config window.opacity=1
and in the quit hook the same, but with window.opacity=0.5 (or whatever your "default" opacity is supposed to be). (How these hooks are handled differs between different ways to set up your neovim, so I'll excuse myself from making recommendations there. Astronvim has a "simple" autocmd lua, where you need to add an vim.fn.jobstart('alacritty msg config window.opacity=1') to the right dictionary key. I find it dazzingly complicated.)
Alternatively, you could just add simple shell function to your .zshrc or .bashrc (depending on what you use, of course), like
function run_opaque() {
if [[ "$TERM" = "alacritty" ]] ; then
alacritty msg config window.opacity=1
$@
retval=$?
alacritty msg config window.opacity=0.1
return $retval
else
$@
fi
}
# set up e alias for the default $EDITOR; if not set, set that to nvim.
[[ -z "$EDITOR" ]] || export EDITOR=nvim
alias e='run_opaque $EDITOR'
and use the short e to launch your favorite editor;