WITH T AS ( SELECT CAST(CONCAT ('$', IIF(TRY_CAST([Key] AS int) IS NOT NULL, CONCAT ('[', [Key], ']'), '.' + [Key])) AS nvarchar(MAX)) AS Path , [Key] , Value , Type , 1 Lvl FROM OPENJSON (@json_doc) UNION ALL SELECT CAST(CONCAT (T.Path, IIF(TRY_CAST(O.[Key] AS int) IS NOT NULL, CONCAT ('[', O.[Key], ']'), '.' + O.[Key])) AS nvarchar(MAX)) , O.[Key] , O.Value , O.Type , T.Lvl + 1 FROM T CROSS APPLY OPENJSON (T.Value) O WHERE T.Type IN ( 4, 5 )) SELECT Path, T.[Key], T.Value, T.Type, T.Lvl FROM T;
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter