Extracting multiple values/levels from a json string in a SQL Server 2019 database table

Question

How can I achieve this in SQL Server 2019? I have an order table with a lineaggregate column that contains a json string with line item details of what was shipped. I need to fetch each variant ID and quantity into separate rows (1 row per variant id).

Below is a sample of an actual json string:

[
  {
    "id": 4379711799354,
    "variant_id": 31291380727866,
    "title": "*NEW* Kayslin Sneaker in Blush",
    "quantity": 1,
    "sku": "79000212",
    "variant_title": "3Y",
    "vendor": "TESTVENDOR",
    "fulfillment_service": "manual",
    "product_id": 4371426607162,
    "requires_shipping": true,
    "taxable": true,
    "gift_card": false,
    "name": "*NEW* Kayslin Sneaker in Blush - 3Y",
    "variant_inventory_management": "shopify",
    "properties": [
      {
        "name": "Item Ships Separately",
        "value": "✓"
      }
    ],
    "product_exists": true,
    "fulfillable_quantity": 0,
    "grams": 490,
    "price": "68.00",
    "total_discount": "0.00",
    "fulfillment_status": "fulfilled",
    "pre_tax_price": "68.00",
    "price_set": {
      "shop_money": {
        "amount": "68.00",
        "currency_code": "USD"
      },
      "presentment_money": {
        "amount": "68.00",
        "currency_code": "USD"
      }
    },
    "pre_tax_price_set": {
      "shop_money": {
        "amount": "68.00",
        "currency_code": "USD"
      },
      "presentment_money": {
        "amount": "68.00",
        "currency_code": "USD"
      }
    },
    "total_discount_set": {
      "shop_money": {
        "amount": "0.00",
        "currency_code": "USD"
      },
      "presentment_money": {
        "amount": "0.00",
        "currency_code": "USD"
      }
    },
    "discount_allocations": [],
    "admin_graphql_api_id": "gid:\/\/shopify\/LineItem\/4379711799354",
    "tax_lines": [
      {
        "title": "CT STATE TAX",
        "price": "0.00",
        "rate": 0.0,
        "price_set": {
          "shop_money": {
            "amount": "0.00",
            "currency_code": "USD"
          },
          "presentment_money": {
            "amount": "0.00",
            "currency_code": "USD"
          }
        }
      }
    ],
    "origin_location": {
      "id": 624876322874,
      "country_code": "US",
      "province_code": "TX",
      "name": "Joyfolie",
      "address1": "1809 W Frankford Rd #160 ",
      "address2": "",
      "city": "Carrollton",
      "zip": "75007"
    }
  },
  {
    "id": 4379711832122,
    "variant_id": 31031946838074,
    "title": "*NEW* Mama Bird Necklace Set in Gold",
    "quantity": 1,
    "sku": "83000109",
    "variant_title": "",
    "vendor": "TESTVENDOR",
    "fulfillment_service": "manual",
    "product_id": 4320751878202,
    "requires_shipping": true,
    "taxable": true,
    "gift_card": false,
    "name": "*NEW* Mama Bird Necklace Set in Gold",
    "variant_inventory_management": "shopify",
    "properties": [],
    "product_exists": true,
    "fulfillable_quantity": 0,
    "grams": 113,
    "price": "29.50",
    "total_discount": "0.00",
    "fulfillment_status": "fulfilled",
    "pre_tax_price": "29.50",
    "price_set": {
      "shop_money": {
        "amount": "29.50",
        "currency_code": "USD"
      },
      "presentment_money": {
        "amount": "29.50",
        "currency_code": "USD"
      }
    },
    "pre_tax_price_set": {
      "shop_money": {
        "amount": "29.50",
        "currency_code": "USD"
      },
      "presentment_money": {
        "amount": "29.50",
        "currency_code": "USD"
      }
    },
    "total_discount_set": {
      "shop_money": {
        "amount": "0.00",
        "currency_code": "USD"
      },
      "presentment_money": {
        "amount": "0.00",
        "currency_code": "USD"
      }
    },
    "discount_allocations": [],
    "admin_graphql_api_id": "gid:\/\/shopify\/LineItem\/4379711832122",
    "tax_lines": [
      {
        "title": "CT STATE TAX",
        "price": "0.00",
        "rate": 0.0,
        "price_set": {
          "shop_money": {
            "amount": "0.00",
            "currency_code": "USD"
          },
          "presentment_money": {
            "amount":

score 0 · Answer 1 · answered Aug 14 '20 at 07:03

This is not a valid JSON, but if we arrange so as to make it valid in such a way that we get the following one :

{
  "Products": [ 
  {
    "id": 4379711799354,
    "variant_id": 31291380727866,
    "title": "*NEW* Kayslin Sneaker in Blush",
    "quantity": 1,
    "sku": "79000212",
    "variant_title": "3Y",
    "vendor": "TESTVENDOR",
    "fulfillment_service": "manual",
    "product_id": 4371426607162,
    "requires_shipping": true,
    "taxable": true,
    "gift_card": false,
    "name": "*NEW* Kayslin Sneaker in Blush - 3Y",
    "variant_inventory_management": "shopify",
    "properties": [
      {
        "name": "Item Ships Separately",
        "value": "✓"
      }
    ]
    }
,
  {
    "id": 4379711832122,
    "variant_id": 31031946838074,
    "title": "*NEW* Mama Bird Necklace Set in Gold",
    "quantity": 1,
    "sku": "83000109",
    "variant_title": "",
    "vendor": "TESTVENDOR",
    "fulfillment_service": "manual",
    "product_id": 4320751878202,
    "requires_shipping": true,
    "taxable": true,
    "gift_card": false,
    "name": "*NEW* Mama Bird Necklace Set in Gold",
    "variant_inventory_management": "shopify",
    "properties": [],
    "product_exists": true,
    "fulfillable_quantity": 0,
    "grams": 113,
    "price": "29.50",
    "total_discount": "0.00",
    "fulfillment_status": "fulfilled",
    "pre_tax_price": "29.50",
    "price_set": {
      "shop_money": {
        "amount": "29.50",
        "currency_code": "USD"
      },
      "presentment_money": {
        "amount": "29.50",
        "currency_code": "USD"
      }
    },
    "pre_tax_price_set": {
      "shop_money": {
        "amount": "29.50",
        "currency_code": "USD"
      },
      "presentment_money": {
        "amount": "29.50",
        "currency_code": "USD"
      }
    },
    "total_discount_set": {
      "shop_money": {
        "amount": "0.00",
        "currency_code": "USD"
      },
      "presentment_money": {
        "amount": "0.00",
        "currency_code": "USD"
      }
    } } ] }

Then you can extract the elements by using OPENJSON() function with the contribution of CROSS APPLY joins within a query :

SELECT Q2.*
  FROM tab
 CROSS APPLY OPENJSON(JsonData,'$.Products')
             WITH (
                    Products nvarchar(max) '$' AS JSON
                    ) Q1
 CROSS APPLY OPENJSON (Q1.Products) 
             WITH (
                    ID         nvarchar(max) '$.id',
                    variant_id nvarchar(max) '$.variant_id',
                    quantity   nvarchar(max) '$.quantity',
                    properties nvarchar(max) '$.properties[0].name',
                    properties nvarchar(max) '$.properties[0].value',
                    price_shop_money_amt nvarchar(max) '$.price_set.shop_money.amount',
                    price_shop_money_cur nvarchar(max) '$.price_set.shop_money.currency_code'
                  ) Q2

Demo

Thank you for this. I will review tonight. I’m getting this data from CDATA odbc driver for Shopify. It seems the Jason field is getting cut off - my database field is nvarchar(max) that should be correct right? — Frank Bluemke, Aug 15 '20 at 16:52
this error suggests that you're using a different table or column name in your case, while I showed a working one within the fiddle. Can you share the whole code in a fiddle like mine..? @FrankBluemke — Barbaros Özhan, Aug 16 '20 at 13:31

Extracting multiple values/levels from a json string in a SQL Server 2019 database table

1 Answers1