SQL Server 2016中的Json函数 [英] Json functions in SQL Server 2016
本文介绍了SQL Server 2016中的Json函数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是我的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](包含json)的表[Schema]
[Schema].[schema] : Table [Schema] with column [schema] (contain a json)
我可以获取userData.schema数据,但是如果我想拥有userData.schema.form.id,它就不起作用.为什么?
I can get userData.schema data, but if i want to have userData.schema.form.id it doesn't want to work. Why?
推荐答案
假定您将以下文档存储在SQL中:
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.
这篇关于SQL Server 2016中的Json函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文