How finished does the compost have to be

Title basically. I understand mature compost is most likely best, but I don't have mature compost atm, but have a pile which is finishing up with mostly unrecognizable content, except for some straw and dry alfalfa whivh were added at the very end.

So would sifting a bit and using it for an actively aerated compost tea still work/be beneficial to the plants?

Yes indeed.
Sifting a bit before the tea is a good idea, but isn't absolutely necessary.
Go right ahead and do it.