question

As you don't specify how you want to display the data, it's pretty hard to tell how to write the t-sql to get the data into that format.
I can only point you into the direction of good resources on json data in SQL Server. I think Damir Matesic's blog post on reading JSON data is pretty good. Look at the last example of reading a JSON structure with multiple levels for example.

https://blog.matesic.info/post/Read-JSON-data-in-MS-SQL

And given Damir's example JSON, here's an example on how to get sub-values from a structure using JSON_VALUE and OPENJSON.

DECLARE @JSON_data NVARCHAR(MAX) = N'{
                
"Name": "Damir Matešić",
"BlogURL": "http:\/\/blog.matesic.info",
"Born": 1979,
"Spouse":null,
"BornAfterWoodstock": true,
"FavoriteDrinks": [
{"Name": "Gin and tonic","Drink": "Occasionally"},
{"Name": "Craft beer","Drink": "Occasionally"},
{"Name": "Coffe with milk","Drink": "Daily"},
{"Name": "Cold water","Drink": "Daily"}],
"Parents": {"Mom": "Iva","Dad": "Boris"}
}';
SELECT 
Name,
[Blog URL],
Born,
Spouse,
Data.[Favorite drinks],
Mom = JSON_VALUE(Data.Parents,'$.Mom'),
Dad = JSON_VALUE(Data.Parents,'$.Dad')
FROM OPENJSON(@JSON_data) WITH (
  Name NVARCHAR(256) '$.Name',
  [Blog URL] NVARCHAR(256) '$.BlogURL',
  Born INT '$.Born',
  Spouse NVARCHAR(256) '$.Spouse',
  [Favorite drinks] NVARCHAR(MAX) '$.FavoriteDrinks' AS JSON,
  Parents NVARCHAR(MAX) '$.Parents' AS JSON
) Data
;