CODEWITHSUNDEEP
sqlsql-serverjson
Vincent Ducroquet
Vincent Ducroquet

Reputation: 924

Json functions in SQL Server 2016

Here is my JSON

[{
"type": "FormBlock",
"id": "07bac163-1765-1fee-dba7-6668f8d8507f",
"x": 50,
"y": 57,
"width": 120,
"height": 50,
"alpha": 1,
"angle": 0,
"userData": {
"schema":"{"form":[{"id":"1493828935122"},{"id":"1495115355556"}]
}]

My query

SELECT JSON_VALUE((SELECT JSON_QUERY([Schema].[schema],'$[0]') 
                       FROM [dbo].[Schema] WHERE objecttype='test'),'$.userData.schema.form[0].id')

[Schema].[schema] : Table [Schema] with column [schema] (contain a json)

I can get userData.schema data, but if i want to have userData.schema.form.id it doesn't want to work. Why?

Upvotes: 1

Views: 172

Answers (1)

user7593937
user7593937

Reputation: 555

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.

Upvotes: 2

Related Questions