SQL Server 2016中的Json函数

admin

SQL Server 2016中的Json函数

sql

这是我的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"}]
}]

我的查询

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

[Schema]。[schema]：具有列[schema]的表[Schema]（包含json）

我可以获取userData.schema数据，但是如果我想拥有userData.schema.form.id，它就不会工作。为什么？

阅读 239

2021-07-01

共1个答案

admin

假设您有以下文档存储在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"
                    }]
                }
            }'

您可以像这样查询数组：

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'

这是我不久前写的一篇博客文章，但我认为它可以为您提供所需的内容，可以查询数组。

2021-07-01