SQL Server 2016 JSON in existing column

Question

I have been banging my head against the wall, for something that is probably fairly obvious, but no amount of googling has provided me with the answer, or hint that I need. Hopefully the geniuses here can help me :)

I have a table that looks a bit like this:

The JSON is already in my SQL Server table, and is basically the product contents of a basket. The current row, is the transaction of the entire purchase, and the JSON is another subset of each product and their various attributes.

Here are 2 rows of the JSON string as an example:

[{"id":"429ac4e546-11e6-471e","product_id":"dc85bff3ecb24","register_id":"0adaaf5c4a65e37c7","sequence":"0","handle":"Skirts","sku":"20052","name":"Skirts","quantity":1,"price":5,"cost":0,"price_set":1,"discount":-5,"loyalty_value":0.2,"tax":0,"tax_id":"dc85058a-a69e-11e58394d","tax_name":"No Tax","tax_rate":0,"tax_total":0,"price_total":5,"display_retail_price_tax_inclusive":"1","status":"CONFIRMED","attributes":[{"name":"line_note","value":""}]}]

and

[{"id":"09237884-9713-9b6751fe0b85ffd","product_id":"dc85058a-a66b4c06702e13","register_id":"06bf5b9-31e2b4ac9d0a","sequence":"0","handle":"BricaBrac","sku":"20076","name":"Bric a Brac","quantity":1,"price":7,"cost":0,"price_set":1,"discount":-7,"loyalty_value":0.28,"tax":0,"tax_id":"dc85058a-2-54f20388394d","tax_name":"No Tax","tax_rate":0,"tax_total":0,"price_total":7,"display_retail_price_tax_inclusive":"1","status":"CONFIRMED","attributes":[{"name":"line_note","value":""}]},{"id":"09237884-9713-9b601235370","product_id":"dc85058a-a6fe112-6b4bfafb107e","register_id":"06bf537bf6b9-31e2b4ac9d0a","sequence":"1","handle":"LadiesTops","sku":"20040","name":"Ladies Tops","quantity":1,"price":10,"cost":0,"price_set":1,"discount":-10,"loyalty_value":0.4,"tax":0,"tax_id":"dc85058a-a690388394d","tax_name":"No Tax","tax_rate":0,"tax_total":0,"price_total":10,"display_retail_price_tax_inclusive":"1","status":"CONFIRMED","attributes":[{"name":"line_note","value":""}]},{"id":"09237884-9713-9b52007fa6c7d","product_id":"dc85058a-a6fa-b4c06d7ed5a","register_id":"06bf537b-cf6b9-31e2b4ac9d0a","sequence":"2","handle":"DVD","sku":"20077","name":"DVD","quantity":1,"price":3,"cost":0,"price_set":1,"discount":-3,"loyalty_value":0.12,"tax":0,"tax_id":"dc85058a-e5-e112-54f20388394d","tax_name":"No Tax","tax_rate":0,"tax_total":0,"price_total":3,"display_retail_price_tax_inclusive":"1","status":"CONFIRMED","attributes":[{"name":"line_note","value":""}]}]

So what I am trying to achieve is to create a new table from the data in that column. (I can then join the product table to this first table via unique string in the id fields).

Is it possible to do this with the new native JSON that is in sql2016.

My alternative is to do it with a plugin via SSIS but it would be cleaner if I can do it with a stored procedure inside SQL Server itself.

Thanks in advance!

Answer 1

Little effort with simple sql query and you will get there.

make this query as stored procedure and call it when needed..

Edit this query according to your requirement.

Change '%id":"' to '%anything_inside_the_string' and you will get the value.. :)

DECLARE @LOOP_1 INT=1,@NAME NVARCHAR (MAX),@LEFT NVARCHAR(MAX),@loop_2 int=0
SET @NAME='[{"id":"429ac4e546-11e6-471e","product_id":"dc85bff3ecb24","register_id":"0adaaf5c4a65e37c7","sequence":"0","handle":"Skirts","sku":"20052","name":"Skirts","quantity":1,"price":5,"cost":0,"price_set":1,"discount":-5,"loyalty_value":0.2,"tax":0,"tax_id":"dc85058a-a69e-11e58394d","tax_name":"No Tax","tax_rate":0,"tax_total":0,"price_total":5,"display_retail_price_tax_inclusive":"1","status":"CONFIRMED","attributes":[{"name":"line_note","value":""}]}]'

-- First loop started to find where 'id":"' is located
WHILE @LOOP_1!=(SELECT LEN(@NAME))
BEGIN
    SET @LEFT=(LEFT(@NAME,@LOOP_1))
    IF @LEFT LIKE '%id":"' -------- Change '%id":"' to '%product_id":"' and you will get the value.. :)
    BEGIN

        set @NAME=(right(@NAME,len(@name)-@LOOP_1))

        -- Second loop started to find where ',' is located after '"id":"'
        WHILE @loop_2!=(SELECT LEN(@NAME))
        BEGIN

            SET @LEFT=(LEFT(@NAME,@loop_2))
            IF @LEFT LIKE '%,'
            BEGIN
                if left(@name,@loop_2-1)like '%"%'
                SELECT left(@name,@loop_2-2)
                else
                SELECT left(@name,@loop_2-2)
                set @loop_2=(SELECT LEN(@NAME)-1)
                set @loop_1=@loop_2

            END
        SET @loop_2=@loop_2+1
        END

    END
    SET @LOOP_1=@LOOP_1+1
END

Answer 2

Thanks to Prdp's response, that guided me to the answer, which is as below.

SELECT  a.ID, b.*  -- select ID from original table for proofing, and all from table b
FROM reporttest a  -- table name with alias
CROSS apply Openjson([register_sale_products])  -- column name
  WITH (
    id nvarchar(200) '$.id',
    product_id nvarchar(200) '$.product_id',
    register_id nvarchar(200) '$.register_id',
    sequence nvarchar(200) '$.sequence',
    handle nvarchar(200) '$.handle',
    sku nvarchar(200) '$.sku',
    name nvarchar(200) '$.name',
    quantity nvarchar(200) '$.quantity',
    price nvarchar(200) '$.price',
    cost nvarchar(200) '$.cost',
    price_set nvarchar(200) '$.price_set',
    discount nvarchar(200) '$.discount',
    loyalty_value nvarchar(200) '$.loyalty_value',
    tax nvarchar(200) '$.tax',
    tax_id nvarchar(200) '$.tax_id',
    tax_name nvarchar(200) '$.tax_name',
    --No Tax nvarchar(200) '$.No Tax',
    tax_rate nvarchar(200) '$.tax_rate',
    tax_total nvarchar(200) '$.tax_total',
    price_total nvarchar(200) '$.price_total',
    display_retail_price_tax_inclusive nvarchar(200) '$.display_retail_price_tax_inclusive',
    status nvarchar(200) '$.status',
    CONFIRMED nvarchar(200) '$.CONFIRMED',
    attributes nvarchar(200) '$.attributes',
    name nvarchar(200) '$.name',
    line_note nvarchar(200) '$.line_note',
    value nvarchar(200) '$.value'     
        ) b  -- alias the "with" section as table b

Answer 3

Here is one way using OPENJSON to extract the ID from your JSON

SELECT  id
FROM Yourtable
CROSS apply Openjson([register_sale_products])
 WITH (id varchar(500) 'lax $.id') 

There are two path modes in OPENJSON

1. strick
2. lax

Strict : will throw error when the property is not found in the path

lax : This will return NULL when the property is not found in the path. If you did not mention any mode then Lax will be used by default

You can use the above modes based on your requirement

DEMO :

Schema Setup

CREATE TABLE json_test
  (
     json_col VARCHAR(8000)
  )

Sample Data

INSERT INTO json_test
VALUES      ('[{"id":"429ac4e546-11e6-471e","product_id":"dc85bff3ecb24","register_id":"0adaaf5c4a65e37c7","sequence":"0","handle":"Skirts","sku":"20052","name":"Skirts","quantity":1,"price":5,"cost":0,"price_set":1,"discount":-5,"loyalty_value":0.2,"tax":0,"tax_id":"dc85058a-a69e-11e58394d","tax_name":"No Tax","tax_rate":0,"tax_total":0,"price_total":5,"display_retail_price_tax_inclusive":"1","status":"CONFIRMED","attributes":[{"name":"line_note","value":""}]}]'),
            ('[{"id":"09237884-9713-9b6751fe0b85ffd","product_id":"dc85058a-a66b4c06702e13","register_id":"06bf5b9-31e2b4ac9d0a","sequence":"0","handle":"BricaBrac","sku":"20076","name":"Bric a Brac","quantity":1,"price":7,"cost":0,"price_set":1,"discount":-7,"loyalty_value":0.28,"tax":0,"tax_id":"dc85058a-2-54f20388394d","tax_name":"No Tax","tax_rate":0,"tax_total":0,"price_total":7,"display_retail_price_tax_inclusive":"1","status":"CONFIRMED","attributes":[{"name":"line_note","value":""}]},{"id":"09237884-9713-9b601235370","product_id":"dc85058a-a6fe112-6b4bfafb107e","register_id":"06bf537bf6b9-31e2b4ac9d0a","sequence":"1","handle":"LadiesTops","sku":"20040","name":"Ladies Tops","quantity":1,"price":10,"cost":0,"price_set":1,"discount":-10,"loyalty_value":0.4,"tax":0,"tax_id":"dc85058a-a690388394d","tax_name":"No Tax","tax_rate":0,"tax_total":0,"price_total":10,"display_retail_price_tax_inclusive":"1","status":"CONFIRMED","attributes":[{"name":"line_note","value":""}]},{"id":"09237884-9713-9b52007fa6c7d","product_id":"dc85058a-a6fa-b4c06d7ed5a","register_id":"06bf537b-cf6b9-31e2b4ac9d0a","sequence":"2","handle":"DVD","sku":"20077","name":"DVD","quantity":1,"price":3,"cost":0,"price_set":1,"discount":-3,"loyalty_value":0.12,"tax":0,"tax_id":"dc85058a-e5-e112-54f20388394d","tax_name":"No Tax","tax_rate":0,"tax_total":0,"price_total":3,"display_retail_price_tax_inclusive":"1","status":"CONFIRMED","attributes":[{"name":"line_note","value":""}]}]')

Query

SELECT  id
FROM json_test
CROSS apply Openjson(json_col)
      WITH (id varchar(500) 'lax $.id')

Result :

╔═══════════════════════════════╗
║              id               ║
╠═══════════════════════════════╣
║ 429ac4e546-11e6-471e          ║
║ 09237884-9713-9b6751fe0b85ffd ║
║ 09237884-9713-9b601235370     ║
║ 09237884-9713-9b52007fa6c7d   ║
║ 429ac4e546-11e6-471e          ║
║ 09237884-9713-9b6751fe0b85ffd ║
║ 09237884-9713-9b601235370     ║
║ 09237884-9713-9b52007fa6c7d   ║
╚═══════════════════════════════╝