Блог

Я бился головой о стену за то, что, вероятно, довольно очевидно, но никакое количество поисковых запросов не дало мне ответа или подсказки, которые мне нужны. Надеюсь, гении здесь могут мне помочь 🙂

У меня есть таблица, которая выглядит примерно так:

JSON уже находится в моей таблице SQL Server и в основном представляет собой содержимое корзины. Текущая строка — это транзакция всей покупки, а JSON — это другое подмножество каждого продукта и их различных атрибутов.

Вот 2 строки строки JSON в качестве примера:

 [{"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":""}]}]
  

Итак, чего я пытаюсь добиться, так это создать новую таблицу из данных в этом столбце. (Затем я могу присоединить таблицу продуктов к этой первой таблице с помощью уникальной строки в полях идентификатора).

Возможно ли это сделать с помощью нового собственного JSON, который находится в sql2016.

Моя альтернатива — сделать это с помощью плагина через SSIS, но было бы чище, если бы я мог сделать это с помощью хранимой процедуры внутри самого SQL Server.

Заранее спасибо!

Вот один из способов OPENJSON извлечения ID из вашего JSON

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

В OPENJSON

1. strick
2. слабый

Strict : выдаст ошибку, если property не найден в path

lax : Это вернет NULL , когда property не найдено в path . Если вы не указали какой-либо режим, то Lax он будет использоваться по умолчанию

Вы можете использовать вышеуказанные режимы в зависимости от ваших требований

ДЕМОНСТРАЦИЯ :

Настройка схемы

 CREATE TABLE json_test
  (
     json_col VARCHAR(8000)
  )
  

Пример данных

 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":""}]}]')
  

Запрос

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

Результат :

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

Благодаря ответу Prdp это привело меня к ответу, который приведен ниже.

 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
  

Небольшое усилие с помощью простого sql-запроса, и вы добьетесь своего.

сделайте этот запрос как хранимую процедуру и вызывайте его при необходимости..

Отредактируйте этот запрос в соответствии с вашими требованиями.

Измените ‘%id»:»‘ на ‘%anything_inside_the_string’, и вы получите значение .. 🙂

 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