Explore contents of a ZIP/Archive from Finder? I've lost count how many times I've double-clicked a .ZIP file on Mac to look inside it (like Windows) only for it to auto-extract in the current location, which is rarely my preference.

Does MacOS provide a way for me to explore (and ideally edit) the content of archive files like Windows?
Previewing contents of zip files in the GUI is not available within macOS as it ships out.

You may however install a QuickLook generator to do that
One would be available within 
https://macitbetter.com/BetterZip-Quick-Look-Generator/

The full app is 30-day trialware, but the QuickLook part will still be available after that period has elapsed.

To use it, slect a zip-file and hit Space. 

Manipulating files within an archive is limited.

Specialised for this would be 


  The Archive Browser (non-free)
  
  Quickly view archive contents


Of course, if you are serious about file management, you'd have to ditch the overly limited Finder.app and use professional software to that end.

One alternative worth looking into would be PathFinder:  unlike Finder the workflow "Get Info">"Preview" in PathFinder offers not just a big icon, but also a preview of what's inside.

Most closely resembling your previous experience in use, but not in setting it up, would of course be to combine https://osxfuse.github.io with https://bitbucket.org/agalanin/fuse-zip/downloads/ 


  use-zip is a FUSE file system to navigate, extract, create and modify ZIP and ZIP64 archives based on libzip implemented in C++.
  With fuse-zip you really can work with ZIP archives as real directories. Unlike KIO or Gnome VFS, it can be used in any application without modifications.


If a seamless usage is the goal but the manual setup sounds too complicated for that, then it's worth to take a look at ivoronin/ArchiveMounter:


  Mounts archives like disk images (macOS)


If you 'install' that application and drag its icon also to the Finder toolbar, (in macOS this was made slightly more complicated: hold Cmd and then drag the app to the toolbar until you see a plus sign) you have a convenient, low-cost solution, that allows you to look inside the archives, extract single files as needed and even manipulate the contents without a full decompression (and/or re-compression). Although that last feature seems to be judged as not that stable by the developer, as it's turned off by default: without setting any preferences it defaults to 'mount' archives read-only.

To solve the issue with double-click resulting in an undesired action (full decompression), you then have to choose from one of the options listed here your favourite method. Then right-click on a zip-file and choose "Get Info". Set your desired action accordingly under the heading "Open With".