JSON is supported inward SQL Server too it is real slow to obtain JSON formatted reply from a interrogation easily. For example,
piece connected to Northwind database you lot acquire the JSON formatted information past times running a interrogation such equally this 1 using the Products table:
---------------------------------
SELECT ProductName, QuantityPerUnit, UnitPrice
FROM Products FOR JSON Auto;
---------------------------------
The reply volition hold out equally shown:
JSON_0
You could besides provide a unmarried row of information equally shown past times running this query:
-----------------------
SELECT [ProductID]
,[ProductName]
FROM [Northwind].[dbo].[Products]
WHERE ProductID=5
For JSON AUTO
-------------------------
JSON_1
The effect comes inward an array fifty-fifty if the returned information is simply 1 row.
You could take the array wrapper ([ ]) surrounding the effect develop past times the next query:
-------------------
SELECT [ProductID]
,[ProductName]
FROM [Northwind].[dbo].[Products]
For JSON AUTO, WITHOUT_ARRAY_WRAPPER
---------------------------------------
JSON_2
Notice that the array wrapper is gone inward the effect set.
You tin cease farther qualify where the information (Which table, for example) past times adding a 'root' chemical gene past times issuing this query:
------------------------
SELECT [ProductID]
,[ProductName]
FROM [Northwind].[dbo].[Products]
For JSON AUTO, Root('Products')
------------------------
JSON_3
You are non allowed to purpose WITHOUT_ARRAY_WRAPPER too Root inward the same sql interrogation because you lot acquire the next message, if you lot do:
Msg 13620, Level 16, State 1, Line 5
ROOT selection too WITHOUT_ARRAY_WRAPPER selection cannot hold out used together inward FOR JSON. Remove 1 of these options.
If you lot are novel to JSON read the following:
https://hlthnworkout.blogspot.com//search?q=how-do-you-work-with-javascript-object
If you lot are motivated to larn in that place are to a greater extent than here:
https://hlthnworkout.blogspot.com/search?q=json
piece connected to Northwind database you lot acquire the JSON formatted information past times running a interrogation such equally this 1 using the Products table:
---------------------------------
SELECT ProductName, QuantityPerUnit, UnitPrice
FROM Products FOR JSON Auto;
---------------------------------
The reply volition hold out equally shown:
JSON_0
You could besides provide a unmarried row of information equally shown past times running this query:
-----------------------
SELECT [ProductID]
,[ProductName]
FROM [Northwind].[dbo].[Products]
WHERE ProductID=5
For JSON AUTO
-------------------------
JSON_1
The effect comes inward an array fifty-fifty if the returned information is simply 1 row.
You could take the array wrapper ([ ]) surrounding the effect develop past times the next query:
-------------------
SELECT [ProductID]
,[ProductName]
FROM [Northwind].[dbo].[Products]
For JSON AUTO, WITHOUT_ARRAY_WRAPPER
---------------------------------------
JSON_2
Notice that the array wrapper is gone inward the effect set.
You tin cease farther qualify where the information (Which table, for example) past times adding a 'root' chemical gene past times issuing this query:
------------------------
SELECT [ProductID]
,[ProductName]
FROM [Northwind].[dbo].[Products]
For JSON AUTO, Root('Products')
------------------------
JSON_3
You are non allowed to purpose WITHOUT_ARRAY_WRAPPER too Root inward the same sql interrogation because you lot acquire the next message, if you lot do:
Msg 13620, Level 16, State 1, Line 5
ROOT selection too WITHOUT_ARRAY_WRAPPER selection cannot hold out used together inward FOR JSON. Remove 1 of these options.
I am using SQL Server 2016 Developer's edition on my Windows 10 Pro laptop.
If you lot are novel to JSON read the following:
https://hlthnworkout.blogspot.com//search?q=how-do-you-work-with-javascript-object
If you lot are motivated to larn in that place are to a greater extent than here:
https://hlthnworkout.blogspot.com/search?q=json
Komentar
Posting Komentar