Eliminating words in citation format, either "doi" or "Retrieved from"

I understand that you want to remove "Retrieved from" from the url display. This is controlled in apacite by BRetrievedFrom. You can renew that command to eliminate the string. A simpler approach is to use the note = {} entry to hold the URL (see at bottom of this post).

I made a short example (using the article class) to illustrate the first approach. I also included some style commands to approximate the AGU style observed on the link you gave (more work would be needed to precisely match), but these are not important to the question you asked.

documentclass{article}

usepackage[%
    pdfborder={0 0 0},%  <=== make some edits toward AGU style links (on the web page mentioned)
    colorlinks=true,%
    linkcolor=blue, %
    citecolor=blue, % 
    urlcolor=blue,  % 
    breaklinks=true,%
            ]{hyperref}
usepackage{url}    % <== linebreaking in url       

usepackage{apacite} 
AtBeginDocument{%
   renewcommand{BRetrievedFrom}{}  % <=== This addresses your main request (no "Retrieved from")
   renewcommand{BAstyle}{itshape} % <=== made some other edits toward AGU style...
   urlstyle{APACsame}%
   renewcommand{BBAA}{and}%
   renewcommand{BBOP}{[}%
   renewcommand{BBCP}{]}%
}

%% let's have a boldface volume number like AGU
makeatletter
renewcommand{APACjournalVolNumPages}[4]{% 
  Bem{#1}%             journal
  ifx@empty#2@empty
  else
    unskip, {bfseries #2}%  volume          %%% <=== changed Bem{#2} to {bfseries #2}
  fi
  ifx@empty#3@empty
  else
    unskip ({#3})%    issue number, with the added leading space
  fi
  ifx@empty#4@empty
  else
    unskip, {#4}%      pages
  fi
}%
makeatother


% sample .bib file
begin{filecontents*}{jobname.bib}  
    @article{aguref,
    author  = {Pablo Stackexchanger and Agu Journal},
    journal = {Journal of Geophysical Research: Solid Earth},
    volume  = {109},
    pages   = {1469--1481},
    year    = {2004},
    title   = {How to include the {URL} without seeing `{R}etrieved from'},
    url     = {https://agupubs.onlinelibrary.wiley.com/doi/abs/10.1029/2004JB003084},
}
end{filecontents*}   

begin{document}
We can use the URL field of texttt{apacite} as desired by citeA{aguref}.  

bibliographystyle{apacite}
bibliography{jobname}

end{document}

A couple further comments:

1. The default BRetrievedfrom is renewcommand{BRetrievedFrom}{Retrieved from }. (There is also a BRetrieved defined as renewcommand{BRetrieved}[1]{Retrieved {#1}, from }, where the argument is the urldate entry.)

2. A very simple way to include a url is through the note = {}, entry, especially for bst styles that do not support url = {}. For example, add to your citation:

   note = {https://agupubs.onlinelibrary.wiley.com/doi/abs/10.1029/2004JB003084}

There is an old AGU bst style (v2.2, 2003) which does not have a url entry, although you could use note as above. Today's AGU Latex template (agujournal2019.cls) is using apacite as the bibliographystyle. The class includes some customization. So, you could make a simpler modification if you are using that class file:

documentclass{agujournal2019}

AtBeginDocument{renewcommand{BRetrievedFrom}{}}  % <=== This addresses your request (no "Retrieved from")

% sample .bib file
begin{filecontents*}{jobname.bib}  
    @article{aguref,
    author  = {Pablo Stackexchanger and Agu Journal},
    journal = {Journal of Geophysical Research: Solid Earth},
    volume  = {109},
    pages   = {1469--1481},
    year    = {2004},
    title   = {How to include the {URL} without seeing `{R}etrieved from'},
    url     = {https://agupubs.onlinelibrary.wiley.com/doi/abs/10.1029/2004JB003084},
}
end{filecontents*}   

begin{document}
We can use the URL field of texttt{apacite} as desired by citeA{aguref}.  

%bibliographystyle{apacite} % <==== OMIT, because the class issues this command
bibliography{jobname}

end{document}

Here's the result:

Note that the change to BRetrievedFrom needs to be delayed to the beginning of the document as done here.

Partially answered, one can remove the doi prefix using

    renewcommand{doiprefix}{}

More info at doi hyperlinks show up as "doi:doi" in my references