Process JSON in SQL Server using OPENJSON

Aim: Very occasionally I have to process JSON in SQL Server, usually as part of a data warehouse task. Because I don’t do this often, I find I have to rebuild my knowledge of the relevant statement every time. So I’ve decided to record a sample T-SQL query that I can refer back to & tailor to my needs.

I’ve downloaded a sample of JSON from https://docs.microsoft.com/en-us/bingads/shopping-content/json-xml-examples. This short sample contains data nested at different levels & arrays, & is therefore a useful starting point for my query as these are likely to occur in my real world data too. It can be helpful to format a sample of the JSON so that it is properly indented (as below) to easily see what level each data value is at. This can be done via a number of online formatting tools or plugins for text editors like Notepad++.

First, declare a variable to hold the JSON. Alternatively, the JSON string(s) can be stored in a column in a table. As SQL Server does not have a built-in JSON data type, use NVARCHAR(MAX).

DECLARE @json NVARCHAR(MAX) = '{
  "additionalImageLinks": [
    "http://www.tailspintoys.com/images?id=123def",
    "http://www.tailspintoys.com/images?id=567def"
  ],
  "adult": "False",
  "adwordsRedirect": "http://contoso.com/hury",
  "ageGroup": "kids",
  "availability": "in stock",
  "brand": "Tailspin",
  "channel": "online",
  "color": "Blue",
  "condition": "new",
  "contentLanguage": "en",
  "description": "Charming sundress perfect for lunch out on the town.",
  "expirationDate": "2016-07-14T07:27:06-08:00",
  "gender": "female",
  "id": "Online:en:US:sku1234",
  "identifierExists": "True",
  "imageLink": "http://www.tailspintoys.com/images?id=123abc",
  "itemGroupId": "abc123def456",
  "kind": "content#product",
  "link": "http://www.tailspintoys.com/girls/apparel?id=9d0s-a934",
  "material": "cotton",
  "offerId": "sku1234",
  "onlineOnly": "False",
  "price": {
    "currency": "USD",
    "value": 38.0000000
  },
  "productType": "Apparel & Accessories > Clothing > Dresses",
  "salePrice": {
    "currency": "USD",
    "value": 25.0000000
  },
  "salePriceEffectiveDate": "2016-06-14T08:00:00-08:00/2016-06-21T17:00:00-08:00",
  "shipping": [
    {
      "country": "US",
      "price": {
        "currency": "USD",
        "value": 3.00
      },
      "region": "CA",
      "service": "Ground"
    }
  ],
  "shippingWeight": {
    "unit": "oz",
    "value": 1.3
  },
  "shippingLabel": "promotion",
  "sizes": [
    "2T",
    "3T"
  ],
  "sizeSystem": "US",
  "sizeType": "regular",
  "targetCountry": "US",
  "taxes": [
    {
      "country": "US",
      "rate": "9.9",
      "region": "CA",
      "taxShip": "True"
    }
  ],
  "title": "Cute Toddler Sundress"
}'

The following query will process the data & can be amended for other similar datasets.

-- Since there is no JSON data type that would help to validate the JSON string, use ISJSON
IF ISJSON(@json) = 1
BEGIN
  
  -- If it is valid, shred it. The output can then be inserted into a table
  SELECT [Details].[Brand]
      ,[Details].[Title]
      ,[Details].[Description]
      ,[Details].[Currency]
      ,[Details].[Price]
      ,[Details].[SalePrice]
      ,[Shipping].[ShippingCurrency]
      ,[Shipping].[ShippingCountry]
      ,[Shipping].[ShippingCost]
  FROM OPENJSON(@json)
  WITH (
    [Brand] NVARCHAR(32) '$.brand' -- top level
    ,[Description] NVARCHAR(64) '$.description' -- top level
    ,[Currency] NCHAR(3) '$.price.currency' -- one level down
    ,[Price] DECIMAL(9, 6) '$.price.value' -- one level down
    ,[SalePrice] DECIMAL(9, 6) '$.salePrice.value' -- one level down
    ,[Title] NVARCHAR(32) '$.title' -- top level
    ,[shippingJSON] NVARCHAR(MAX) '$.shipping' AS JSON -- an array can be dealt with as a separate JSON string & CROSS APPLYed
  ) AS [Details]
  CROSS APPLY OPENJSON([Details].[shippingJSON])
  WITH (
    [ShippingCurrency] NCHAR(3) '$.price.currency' -- one level down in the array
    ,[ShippingCost] DECIMAL(9, 6) '$.price.value' -- one level down in the array
    ,[ShippingCountry] NVARCHAR(32) '$.country' -- top level in the array
  ) AS [Shipping];

END
ELSE
BEGIN

  PRINT '@json does not contain valid JSON'

END

The output is:

Next: I plan to repeat this exercise for XML data because, as with JSON, I occasionally have to process it for data warehouse projects & have the same problems remembering the exact syntax for the relevant statement.