The entire mission was a Militech ordered illegal Black op. The objective was to seize and capture any and all Arasaka assets, specially their infrastructure and SoulKiller. If for whatever reason seize and capture wasnt possible their mission was to blow the entire building up with a tactical nuclear device. The reason of why they wanted to do this is long to explain but essentially there was a "cold war" scenario between Militech and Arasaka during the Fourth Corporate War in which both corporations hired mercenaries to screw with their rival without legally declaring war at each other, this period was called the Shadow War. Pretty much CIA vs KGB so to speak.
For this they commissioned a secret special strike team that included some special forces soldiers AND Blackhand, they were divided in Beta Team (Militech) and Omega Team (Blackhand and a handpicked team he selected). ONLY them were aware of the nuclear device. Blackhand also hired a team of mercenaries and edgerunners which included Spider Murphy, Rogue, Shaitan, a group of Aldecaldos known as Lobos and Thompson, called Alpha Team. Spider went out of her way and called Johnny in order to invite him to her team for the attack. Why? because THEIR objective was to free AI Alt since she had been trapped in Arasaka servers since 2013 (thats when Never Fade Away flashback happens and Alt is SoulKilled by Arasaka).
So Alpha Team was tasked with being the distraction while also having their own objective on the tower. They launch in the building and proceed to make their way into the labs. One of the Lobos was saw Blackhand running down stairs with a big suitcase, presumably the nuclear bomb.
Reaching the lab, Spider was able to contact Alt and download her inside a laptop. They were promptly ambushed by Adam Smasher and special forces team of Arasaka. Spider immediately used the laptop to connect into the net and upload and scatter various portions of Alt. Thompson ended up being heavily injured. Johnny watching his teammates struggle and feeling he was letting Alt down again acted as a catalyst for his cyber psychosis and promptly turned around guns in hand and provoked Smasher. Smasher barely reacted and one-shot him with his shotgun. Spider starts screaming and crying, they were friends for many years and she really respected and loved him as a friend. She grabbed a chip from her pocket, a chip with SoulKiller installed, and plug it inside Johnnys dying skull while whispering she was sorry. Her laptop was destroyed. Shaitan, watching Johnnys death, grabbed Smasher and yelled the rest of the team to escape. Spider had to leave behind Johnnys body with the chip still installed.
Reaching the Top of the tower to get into the AV they meet Blackhand and some of his team. They are intercepted by Smasher, he was holding the still alive head of Shaitan in his hand (Shaitan was a full borg much like Smasher). Blackhand step down from the AV and confronted him, telling his allies to escape. This is the last time Blackhand is canonically seen alive (we know he is still alive thanks to Mike Pondsmith but there are no more stories from him)
While this is happening Beta Team (Militech special forces) were caught in a firefight with Arasaka soldiers inside the tower, somehow the action caused an early detonation of the nuclear device.
A few minutes later the team escaping on the AV saw the nuke going off, instantly decimating a huge portion of Night City.
All of this is narrated in Cyberpunk RED https://cyberpunk.fandom.com/wiki/Cyberpunk_RED, specially The Fall of the Towers story https://cyberpunk.fandom.com/wiki/The_Fall_of_the_Towers , also Firestorm Shockwave from Cyberpunk 2020 https://cyberpunk.fandom.com/wiki/Firestorm_Shockwave
There is a more complete version here https://cyberpunk.fandom.com/wiki/Night_City_Holocaust
Soon afterwards the Time of the RED started. A near apocalypse event characterized by the blood red skies, formed because of the pollution, contamination and radiation of the nuclear attack, not only in Night City but in the entire world. A farewell gift of the Fourth Corporate War.
Johnnys corpse survived the explosion and is PRESUMABLY sometime afterwards found by Arasaka. They kept the chip, with his now engram installed, and its assumed they discarded his body as it was simply useless to them. Many years later, after they finished building Mikoshi, they uploaded him into the database.
EDIT: There is no real explanation of how exactly Arasaka managed to get his chip, this is my own explanation of this event having in consideration Black Dog story.
This is where Black Dog story starts: in 2023, shortly after the Attack, Samantha Stevens, a SAMURAI fan and NC firefighter gets a call from a friend telling her to go into the Hot Zone, the most radiation contaminated location in Night City and look for a wrecked bunker. She finds Johnny (now twice) abandoned body and kept it in a Cryochamber while searching the debris. She also finds a UNDETONATED nuclear device, WAY bigger than the one Militech used. Presumably Arasaka was planning to use it as a countermeasure but they never got the chance. She disarmed the device and discarded it in the river, causing her severe radiation poisoning and requiring permanent life support and chrome to survive.
Decades later, in the 2040s, she hired a team of mercenaries, including Trace Santiago (the son of Rogue with Nomad Santiago) and tasked them to transport the Cryochamber to the same friend who contacted her all those years ago in exchange of a data shard containing the, then unknown and hidden, Black Dog song (this was the last song Johnny ever produced before going to the Tower).
Her friend is called Angel, she has large blonde hair, a cyberware arm and is described as extremely beautiful. She greets the body of Johnny saying "Hello love". Its HEAVILY implied she is Alt. This is the last piece of lore, the rest is seen in 2077.
Black Dog short story https://cyberpunk.fandom.com/wiki/Black_Dog_(story))
EDIT: wording