Thursday, March 8, 2018

Sql and JSON

--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'
  )

No comments:

Post a Comment