Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- )
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement