Alias not available with GROUP BY while ORDER BY is working fine

Question

The main idea here is to extract json values from a column (genre tags from a Movie DB) and create a new table containing all the genres. This is done in serveral step, first I checked the max genre for a movie (here 7), then generate 7 tables to merge together.

This snippet is designed to create the 7 tables.

DECLARE @i INT, @str_id VARCHAR(10), @str_name VARCHAR(10)

SET @i = 0
WHILE(@i < 7)
BEGIN
    SET @str_id = '$[' + CAST(@i AS CHAR(1)) + '].id' 
    SET @str_name = '$[' + CAST(@i AS CHAR(1)) + '].name' 

    SELECT JSON_VALUE(genres, @str_id) AS temp_id, 
        JSON_VALUE(genres, @str_name) AS temp_name 
--  FROM Tmdb_orig ORDER BY temp_id DESC
    FROM Tmdb_orig GROUP BY temp_id
    
    SET @i = @i + 1
END

But I get this error message:

Msg 207, Level 16, State 1, Line 12
Invalid column name 'temp_id'.

While the commented line (ORDER BY) using the same alias is working fine.

Is that a bug ? a misunderstanding from me ?

I could do that with another langage easily (Python for instance) but I want to learn sql-server as much as I can.

FYI: DB structure with the 5 first entries:

CREATE TABLE Tmdb_orig(
   budget               BIGINT  NOT NULL
  ,genres               VARCHAR(230) NOT NULL
  ,homepage             VARCHAR(138)
  ,id                   INTEGER  NOT NULL PRIMARY KEY 
  ,keywords             VARCHAR(3783) NOT NULL
  ,original_language    VARCHAR(2) NOT NULL
  ,original_title       VARCHAR(86) NOT NULL
  ,overview             VARCHAR(1000)
  ,popularity           NUMERIC(10,6) NOT NULL
  ,production_companies VARCHAR(1155) NOT NULL
  ,production_countries VARCHAR(517) NOT NULL
  ,release_date         DATE 
  ,revenue              BIGINT  NOT NULL
  ,runtime              NUMERIC(5,1)
  ,spoken_languages     VARCHAR(474) NOT NULL
  ,status               VARCHAR(15) NOT NULL
  ,tagline              VARCHAR(252)
  ,title                VARCHAR(86) NOT NULL
  ,vote_average         NUMERIC(4,1) NOT NULL
  ,vote_count           INTEGER  NOT NULL
);
INSERT INTO Tmdb_orig(budget,genres,homepage,id,keywords,original_language,original_title,overview,popularity,production_companies,production_countries,release_date,revenue,runtime,spoken_languages,status,tagline,title,vote_average,vote_count) VALUES (237000000,'[{"id": 28, "name": "Action"}, {"id": 12, "name": "Adventure"}, {"id": 14, "name": "Fantasy"}, {"id": 878, "name": "Science Fiction"}]','http://www.avatarmovie.com/',19995,'[{"id": 1463, "name": "culture clash"}, {"id": 2964, "name": "future"}, {"id": 3386, "name": "space war"}, {"id": 3388, "name": "space colony"}, {"id": 3679, "name": "society"}, {"id": 3801, "name": "space travel"}, {"id": 9685, "name": "futuristic"}, {"id": 9840, "name": "romance"}, {"id": 9882, "name": "space"}, {"id": 9951, "name": "alien"}, {"id": 10148, "name": "tribe"}, {"id": 10158, "name": "alien planet"}, {"id": 10987, "name": "cgi"}, {"id": 11399, "name": "marine"}, {"id": 13065, "name": "soldier"}, {"id": 14643, "name": "battle"}, {"id": 14720, "name": "love affair"}, {"id": 165431, "name": "anti war"}, {"id": 193554, "name": "power relations"}, {"id": 206690, "name": "mind and soul"}, {"id": 209714, "name": "3d"}]','en','Avatar','In the 22nd century, a paraplegic Marine is dispatched to the moon Pandora on a unique mission, but becomes torn between following orders and protecting an alien civilization.',150.437577,'[{"name": "Ingenious Film Partners", "id": 289}, {"name": "Twentieth Century Fox Film Corporation", "id": 306}, {"name": "Dune Entertainment", "id": 444}, {"name": "Lightstorm Entertainment", "id": 574}]','[{"iso_3166_1": "US", "name": "United States of America"}, {"iso_3166_1": "GB", "name": "United Kingdom"}]','2009-12-10',2787965087,162,'[{"iso_639_1": "en", "name": "English"}, {"iso_639_1": "es", "name": "Espa\u00f1ol"}]','Released','Enter the World of Pandora.','Avatar',7.2,11800);
INSERT INTO Tmdb_orig(budget,genres,homepage,id,keywords,original_language,original_title,overview,popularity,production_companies,production_countries,release_date,revenue,runtime,spoken_languages,status,tagline,title,vote_average,vote_count) VALUES (300000000,'[{"id": 12, "name": "Adventure"}, {"id": 14, "name": "Fantasy"}, {"id": 28, "name": "Action"}]','http://disney.go.com/disneypictures/pirates/',285,'[{"id": 270, "name": "ocean"}, {"id": 726, "name": "drug abuse"}, {"id": 911, "name": "exotic island"}, {"id": 1319, "name": "east india trading company"}, {"id": 2038, "name": "love of one''s life"}, {"id": 2052, "name": "traitor"}, {"id": 2580, "name": "shipwreck"}, {"id": 2660, "name": "strong woman"}, {"id": 3799, "name": "ship"}, {"id": 5740, "name": "alliance"}, {"id": 5941, "name": "calypso"}, {"id": 6155, "name": "afterlife"}, {"id": 6211, "name": "fighter"}, {"id": 12988, "name": "pirate"}, {"id": 157186, "name": "swashbuckler"}, {"id": 179430, "name": "aftercreditsstinger"}]','en','Pirates of the Caribbean: At World''s End','Captain Barbossa, long believed to be dead, has come back to life and is headed to the edge of the Earth with Will Turner and Elizabeth Swann. But nothing is quite as it seems.',139.082615,'[{"name": "Walt Disney Pictures", "id": 2}, {"name": "Jerry Bruckheimer Films", "id": 130}, {"name": "Second Mate Productions", "id": 19936}]','[{"iso_3166_1": "US", "name": "United States of America"}]','2007-05-19',961000000,169,'[{"iso_639_1": "en", "name": "English"}]','Released','At the end of the world, the adventure begins.','Pirates of the Caribbean: At World''s End',6.9,4500);
INSERT INTO Tmdb_orig(budget,genres,homepage,id,keywords,original_language,original_title,overview,popularity,production_companies,production_countries,release_date,revenue,runtime,spoken_languages,status,tagline,title,vote_average,vote_count) VALUES (245000000,'[{"id": 28, "name": "Action"}, {"id": 12, "name": "Adventure"}, {"id": 80, "name": "Crime"}]','http://www.sonypictures.com/movies/spectre/',206647,'[{"id": 470, "name": "spy"}, {"id": 818, "name": "based on novel"}, {"id": 4289, "name": "secret agent"}, {"id": 9663, "name": "sequel"}, {"id": 14555, "name": "mi6"}, {"id": 156095, "name": "british secret service"}, {"id": 158431, "name": "united kingdom"}]','en','Spectre','A cryptic message from Bond’s past sends him on a trail to uncover a sinister organization. While M battles political forces to keep the secret service alive, Bond peels back the layers of deceit to reveal the terrible truth behind SPECTRE.',107.376788,'[{"name": "Columbia Pictures", "id": 5}, {"name": "Danjaq", "id": 10761}, {"name": "B24", "id": 69434}]','[{"iso_3166_1": "GB", "name": "United Kingdom"}, {"iso_3166_1": "US", "name": "United States of America"}]','2015-10-26',880674609,148,'[{"iso_639_1": "fr", "name": "Fran\u00e7ais"}, {"iso_639_1": "en", "name": "English"}, {"iso_639_1": "es", "name": "Espa\u00f1ol"}, {"iso_639_1": "it", "name": "Italiano"}, {"iso_639_1": "de", "name": "Deutsch"}]','Released','A Plan No One Escapes','Spectre',6.3,4466);
INSERT INTO Tmdb_orig(budget,genres,homepage,id,keywords,original_language,original_title,overview,popularity,production_companies,production_countries,release_date,revenue,runtime,spoken_languages,status,tagline,title,vote_average,vote_count) VALUES (250000000,'[{"id": 28, "name": "Action"}, {"id": 80, "name": "Crime"}, {"id": 18, "name": "Drama"}, {"id": 53, "name": "Thriller"}]','http://www.thedarkknightrises.com/',49026,'[{"id": 849, "name": "dc comics"}, {"id": 853, "name": "crime fighter"}, {"id": 949, "name": "terrorist"}, {"id": 1308, "name": "secret identity"}, {"id": 1437, "name": "burglar"}, {"id": 3051, "name": "hostage drama"}, {"id": 3562, "name": "time bomb"}, {"id": 6969, "name": "gotham city"}, {"id": 7002, "name": "vigilante"}, {"id": 9665, "name": "cover-up"}, {"id": 9715, "name": "superhero"}, {"id": 9990, "name": "villainess"}, {"id": 10044, "name": "tragic hero"}, {"id": 13015, "name": "terrorism"}, {"id": 14796, "name": "destruction"}, {"id": 18933, "name": "catwoman"}, {"id": 156082, "name": "cat burglar"}, {"id": 156395, "name": "imax"}, {"id": 173272, "name": "flood"}, {"id": 179093, "name": "criminal underworld"}, {"id": 230775, "name": "batman"}]','en','The Dark Knight Rises','Following the death of District Attorney Harvey Dent, Batman assumes responsibility for Dent''s crimes to protect the late attorney''s reputation and is subsequently hunted by the Gotham City Police Department. Eight years later, Batman encounters the mysterious Selina Kyle and the villainous Bane, a new terrorist leader who overwhelms Gotham''s finest. The Dark Knight resurfaces to protect a city that has branded him an enemy.',112.31295,'[{"name": "Legendary Pictures", "id": 923}, {"name": "Warner Bros.", "id": 6194}, {"name": "DC Entertainment", "id": 9993}, {"name": "Syncopy", "id": 9996}]','[{"iso_3166_1": "US", "name": "United States of America"}]','2012-07-16',1084939099,165,'[{"iso_639_1": "en", "name": "English"}]','Released','The Legend Ends','The Dark Knight Rises',7.6,9106);
INSERT INTO Tmdb_orig(budget,genres,homepage,id,keywords,original_language,original_title,overview,popularity,production_companies,production_countries,release_date,revenue,runtime,spoken_languages,status,tagline,title,vote_average,vote_count) VALUES (260000000,'[{"id": 28, "name": "Action"}, {"id": 12, "name": "Adventure"}, {"id": 878, "name": "Science Fiction"}]','http://movies.disney.com/john-carter',49529,'[{"id": 818, "name": "based on novel"}, {"id": 839, "name": "mars"}, {"id": 1456, "name": "medallion"}, {"id": 3801, "name": "space travel"}, {"id": 7376, "name": "princess"}, {"id": 9951, "name": "alien"}, {"id": 10028, "name": "steampunk"}, {"id": 10539, "name": "martian"}, {"id": 10685, "name": "escape"}, {"id": 161511, "name": "edgar rice burroughs"}, {"id": 163252, "name": "alien race"}, {"id": 179102, "name": "superhuman strength"}, {"id": 190320, "name": "mars civilization"}, {"id": 195446, "name": "sword and planet"}, {"id": 207928, "name": "19th century"}, {"id": 209714, "name": "3d"}]','en','John Carter','John Carter is a war-weary, former military captain who''s inexplicably transported to the mysterious and exotic planet of Barsoom (Mars) and reluctantly becomes embroiled in an epic conflict. It''s a world on the brink of collapse, and Carter rediscovers his humanity when he realizes the survival of Barsoom and its people rests in his hands.',43.926995,'[{"name": "Walt Disney Pictures", "id": 2}]','[{"iso_3166_1": "US", "name": "United States of America"}]','2012-03-07',284139100,132,'[{"iso_639_1": "en", "name": "English"}]','Released','Lost in our world, found in another.','John Carter',6.1,2124);