You can see some of the blue collar edge which made his earlier films so watchable, starting to peel away in 'The Abyss'. By the time of 'True Lies', his output is getting generic. 'Titanic', while it's a guilty pleasure to watch, is so superficial, it feels like Spielberg could have directed it.
I personally like 'Avatar' and there are glimpses into the Cameron age of yore lightly sprinkled through it (the beginning, in zero gravity, in particular), but this is 'not your daddy's Jim Cameron'.
He's still a f**king genius and true pioneer (both technologically and as a literal adventurer). When you read some articles on him, he really does come across as some possessed, psychopathic version of Elon Musk, with a strange aquatic infatuation. But if he did an entry in the series today, it wouldn't be like 'Aliens' was. He speaks about how changing his diet apparently made a big change in his mental outlook, but I do wonder if those years of being angry gave him added passionate inspiration. Working with him was often described like being in a war zone for good reason.
I mean, who could have predicted that the reunion of HR Giger and Ridley Scott on an 'Alien' sequel would have resulted in 'Prometheus'? Whether or not you like it, it has an undeniably different atmosphere in every way to his earlier effort.
If Cameron, however, suddenly announced he was getting involved (keeping in mind that he recently declared he no longer had an interest in being so), I'd certainly be interested. A return to science-fiction with a deliberately mature horror theme might be just what he needs to return to that glory era. But I know that whatever we'd get wouldn't match up to my teenage day-dreams of what another Cameron-helmed sequel would have been, way back when.
With that said?
I think a story he wrote would be interesting to watch, even if someone else directed.