假设您有以下文档存储在SQL中：
CREATE TABLE JSonTable ( ID int IDENTITY (1,1) PRIMARY KEY CLUSTERED,JSondocument nvarchar(max) )INSERT INTO JSONTableSELECt '{ "FilmDetails":{ "ProductNumber":"9912088751", "Title":"Brave", "Type":"Movie", "Runtime":93, "ReleaseYear":2012, "Synopses":[ { "Locale":"en", "Text":"Princess Merida uses her bravery and archery skills to battle a curse and restore peace..." }, { "Locale":"de", "Text":"Animiert" }, { "Locale":"fr", "Text":"Au coeur des contr茅es sauvages d脡cosse, Merida, la fille du roi Fergus et de la reine Elinor..."}], "Assets":[ { "Name":"Stickers", "AssetType":"Stickers", "FileType":"PDF", "Location":"http://media.brave.stickers.pdf", "Locale":"en-US" }, { "Name":"Trailer - WMV", "AssetType":"Trailer - WMV", "FileType":"Video", "Location":"http://youtu.be/Shg79Shgn", "Locale":"en-US" }] } }'您可以像这样查询数组：
SELECT JSON_VALUE(JSONdocument, '$.FilmDetails.ProductNumber') as ProductNumber ,JSON_VALUE(JSONdocument, '$.FilmDetails.Title') as Title ,JSON_VALUE(JSONdocument, '$.FilmDetails.Type') as ContentType ,JSON_VALUE(JSONdocument, '$.FilmDetails.Runtime') as Runtime ,JSON_VALUE(JSONdocument, '$.FilmDetails.ReleaseYear') as ReleaseYear ,Locale ,SynopsesText ,Name AS AssetName ,FileType AS AssetFileType ,[Location] AS AssetLocationFROM JSonTableCROSS APPLY OPENJSON(JSONdocument, '$.FilmDetails.Synopses')WITH ( Locale varchar(3) '$.Locale' ,SynopsesText nvarchar(2000) '$.Text') CROSS APPLY OPENJSON(JSONdocument, '$.FilmDetails.Assets')WITH ( Name varchar(25) '$.Name' ,FileType varchar(25) '$.FileType' ,[Location] nvarchar(500) '$.Location' ) WHERe JSON_VALUE(JSONdocument, '$.FilmDetails.Title') LIKE '%Brave%' AND Locale = 'en' AND FileType = 'video'
这是我不久前写的一篇博客文章，但我认为它可以为您提供所需的内容，可以查询数组。
