JSON in SQL Server examples

DECLARE @json NVARCHAR(4000)
SET @json =
N'{
    "info":{
      "type":1,

      "address":{
        "town":"Bristol",
        "county":"Avon",
        "country":"England"
      },
      "tags":["Sport", "Water polo"]
   },
   "type":"Basic"
}'

SELECT JSON_VALUE(@json, '$.info.address.town')
SELECT JSON_QUERY(@json, '$.info.tags')

SELECT * FROM OPENJSON(@json, '$.info')


SELECT top 2 id, header FROM as_trace FOR JSON  auto
--FOR JSON  auto
FOR JSON PATH, ROOT('Orders')


select isjson(@json)


-------------
declare @s nvarchar(max) = '{
    "Orders":
    [
        {
        "Order": {
            "Number": "S043659",
            "Date": "2011-05-31T00:00:00"
        },
        "Account": "Microsoft",
        "Item": {
            "Price": 59.99,
            "Quantity": 1
        }
        },
        {
            "Order": {
                "Number": "S043661",
                "Date": "2011-06-01T00:00:00"
            },
            "Account": "Nokia",
            "Item": {
                "Price": 24.99,
                "Quantity": 3
            }
        }
    ]
}'

SELECT JSON_VALUE(Value, N'$.Order.Number') Number,
     JSON_VALUE(Value, N'$.Order.Date') Date,
     JSON_VALUE(Value, N'$.Account') Customer,
     JSON_VALUE(Value, N'$.Item.Quantity') Quantity


FROM OPENJSON(@s, N'$.Orders')

SELECT *
FROM OPENJSON(@s, N'$.Orders')
WITH (
    Number      VARCHAR(200)    N'$.Order.Number',
    Date        DATETIME        N'$.Order.Date',
    Customer    VARCHAR(200)    N'$.Account',
    Quantity    INT             N'$.Item.Quantity',
    obj    nvarchar(max)   '$.obj' as JSON
)