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!

Can you add the expected output

Pரதீப்
– Pரதீப்

2016-10-18 01:32:47 +00:00
Commented Oct 18, 2016 at 1:32 — Pரதீப்
– Pரதீப், Commented Oct 18, 2016 at 1:32

Pரதீப் · Accepted Answer · 2016-10-18 02:09:45Z

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

strick
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   ║
╚═══════════════════════════════╝

Thanks! Really appreciated. That gets me part of the way there. However I want to return all the parts of the JSON string as a table, not just the ID column. Is that possible?
I think what you have given me is enough for me to create a solution! working on it now, will post back if it works..
So yep, I have the solution thanks to you help. Will post as answer to help others.

Maz · Accepted Answer · 2016-10-18 03:28:24Z

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

marc_s · Accepted Answer · 2016-12-18 18:20:28Z

0

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

edited Dec 18, 2016 at 18:20

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Oct 18, 2016 at 4:19

Raju Prasai

1061 silver badge7 bronze badges

2 Comments

Pரதீப் Over a year ago

Why will you do this when you have native support

Raju Prasai Over a year ago

@prdp i was trying to do the task according to Maz requirement. He was willing to try the solution from sp rather than native JSON.

Collectives™ on Stack Overflow

SQL Server 2016 JSON in existing column

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related