Reputation: 924
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
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