--JSON -SQL Server 2017 /*{ _id : "1", lastName : "Jones", firstName : "Sara", email: "sara.jones@metroalt.com" }*/ Select top 10 EmployeeFirstName, EmployeeLastName, EmployeeEmail From Employee for JSON auto Select BusBarnKey, BusbarnPhone, BusBarnAddress as 'address.street', BusbarnCity as 'address.city', BusBarnZipCode as 'address.zipCode' From BusBarn for JSON Path ALTER DATABASE MetroAlt SET COMPATIBILITY_LEVEL = 130 Declare @JSON nvarchar(max) Set @JSON=N'{"_id" : 1, "product": "IPad", "price" : 894.50, "quantityAvailable" : 13}' Select * From OPENJSON(@JSON) Declare @JSON2 nvarchar(max) Set @JSON2 ='[ {"_id" : 1, "product": "IPad", "price" : 894.50, "quantityAvailable" : 13}, {"_id" : 2, "product": "Chrome Book", "price" : 245.99, "quantityAvailable": 23}, {"_id" : 3, "product": "Bose Lap Top Speakers", "price" : 89.50, "quantityAvailable" : 10}, {"_id" : 4, "product": "Blue Tooth Game Controller", "price" : 149.99, "quantityAvailable" : 3}, {"_id" : 5, "product": "Star Wars Mouse Pad", "price" : 1.50, "quantityAvailable" : 100}, {"_id" : 6, "product": "Dell XPS Desk Top Computer", "price" : 945.00, "quantityAvailable" : 7}, {"_id" : 7, "product": "Microsoft Surface Pro", "price" : 1250.75, "quantityAvailable" : 9}, {"_id" : 8, "product": "Norton Anti Virus", "price" : 75.50, "quantityAvailable": 2}, {"_id" : 9, "product": "Mechanical Keyboard", "price" : 125.50, "quantityAvailable" : 3}, {"_id" : 10, "product": "Android Tablet", "price" : 345.23, "quantityAvailable" : 5} ]' Select * from OpenJSON(@JSON2) with( id int '$._id', product nvarchar(255) '$.product', price decimal(8,2) '$.price', quantity int '$.quantityAvailable' )
Thursday, March 8, 2018
Sql and JSON
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment