Json functions in SQL Server 2016

Assume you have the following document stored in SQL:

    CREATE TABLE JSONTable (
 ID int IDENTITY (1,1) PRIMARY KEY CLUSTERED
,JSONDocument nvarchar(max) )


INSERT INTO JSONTable
SELECT '{
            "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"
                    }]
                }
            }'

You can query in to arrays like such:

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 AssetLocation
FROM JSONTable
CROSS 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'

This is from a blog post I made awhile back, but I think it gives you what you are looking for, querying in to arrays.