Flatten nested JSON data into SQL Server tables

Question

I have a JSON file that contains transactions, and each transaction contains multiple items. I need to flatten the data into SQL Server tables using T-SQL.

I have tried different options to flatten it, but it looks like I am missing something. Anyone who has worked on a similar structure have any ideas how this could be accomplished?

DECLARE @json NVARCHAR(MAX);

SELECT @json = JsonPath
FROM [dbo].[stg_transactionsJson] b;

SELECT 
    CONVERT(NVARCHAR(50), JSON_VALUE(c.Value, '$.orderId')) AS orderId,
    CONVERT(DATETIME, JSON_VALUE(c.Value, '$.openTime')) AS openTime,
    CONVERT(DATETIME, JSON_VALUE(c.Value, '$.closeTime')) AS closeTime,
    CONVERT(NVARCHAR(50), JSON_VALUE(c.Value, '$.operatorId')) AS operatorId,
    CONVERT(NVARCHAR(50), JSON_VALUE(c.Value, '$.terminalId')) AS terminalId,
    CONVERT(NVARCHAR(50), JSON_VALUE(c.Value, '$.sessionId')) AS sessionId,
    CONVERT(NVARCHAR(50), JSON_VALUE(p.Value, '$.productGroupId')) AS productGroupId,
    CONVERT(NVARCHAR(150), JSON_VALUE(p.Value, '$.productId')) AS productId,
    CONVERT(NVARCHAR(50), JSON_VALUE(p.Value, '$.quantity')) AS quantity,
    CONVERT(NVARCHAR(150), JSON_VALUE(p.Value, '$.taxValue')) AS taxValue,
    CONVERT(NVARCHAR(50), JSON_VALUE(p.Value, '$.value')) AS ProductValue,
    CONVERT(NVARCHAR(150), JSON_VALUE(p.Value, '$.priceBandId')) AS priceBandId,
    GETDATE() AS DateUpdated
FROM
    OPENJSON(@json) AS c
OUTER APPLY 
    OPENJSON(c.Value, '$."products"') AS p;

And the sample JSON as follows

{
  "orderId": 431,
  "openTime": "2022-10-31T13:12:28",
  "closeTime": "2022-10-31T13:12:32",
  "operatorId": 7,
  "terminalId": 4,
  "sessionId": 1,
  "products": [
    {
      "productId": 2632,
      "productGroupId": 162,
      "quantity": 1,
      "taxValue": 0.58,
      "value": 3.5,
      "priceBandId": 2
    },
    {
      "productId": 3224,
      "productGroupId": 164,
      "quantity": 1,
      "taxValue": 0.08,
      "value": 0.5,
      "priceBandId": 2
    }
  ],
  "tenders": [
    {
      "tenderId": 2,
      "value": 4.0
    }
  ],
  "type": 1,
  "memberId": 1
}

nbk · Accepted Answer · 2023-01-11 13:59:39Z

2

You can Do it like this

First declare the values in an with clause and then cross apply the products

DECLARE @json NVARCHAR(MAX);
SET @json = '{
  "orderId": 431,
  "openTime": "2022-10-31T13:12:28",
  "closeTime": "2022-10-31T13:12:32",
  "operatorId": 7,
  "terminalId": 4,
  "sessionId": 1,
  "products": [
    {
      "productId": 2632,
      "productGroupId": 162,
      "quantity": 1,
      "taxValue": 0.58,
      "value": 3.5,
      "priceBandId": 2
    },
    {
      "productId": 3224,
      "productGroupId": 164,
      "quantity": 1,
      "taxValue": 0.08,
      "value": 0.5,
      "priceBandId": 2
    }
  ],
  "tenders": [
    {
      "tenderId": 2,
      "value": 4.0
    }
  ],
  "type": 1,
  "memberId": 1
}
';
SELECT c.orderId
  ,c.openTime
  ,c.closeTime
  ,c.operatorId
  ,c.terminalId
  ,c.sessionId
  , JSON_VALUE(p.Value, '$.productGroupId') productGroupId
  , JSON_VALUE(p.Value, '$.productId') productId
  , JSON_VALUE(p.Value, '$.quantity') quantity
  , JSON_VALUE(p.Value, '$.taxValue') taxValue
  , JSON_VALUE(p.Value, '$.value') value
  , JSON_VALUE(p.Value, '$.priceBandId') priceBandId
  ,GETDATE() AS DateUpdated
  FROM
  OPENJSON(@json)  WITH (
  orderId int '$.orderId',
  openTime date '$.openTime',
  closeTime date '$.closeTime',
  operatorId int '$.operatorId',
  terminalId int '$.terminalId',
  sessionId int '$.sessionId',
  [products] NVARCHAR(MAX) as JSON) c
CROSS APPLY OPENJSON(c.products) p

orderId	openTime	closeTime	operatorId	terminalId	sessionId	productGroupId	productId	quantity	taxValue	value	priceBandId	DateUpdated
431	2022-10-31	2022-10-31	7	4	1	162	2632	1	0.58	3.5	2	2023-01-11 13:57:47.607
431	2022-10-31	2022-10-31	7	4	1	164	3224	1	0.08	0.5	2	2023-01-11 13:57:47.607

fiddle

answered Jan 11, 2023 at 13:59

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jobin Over a year ago

Thank you for answering the question, How it could be if I have to use this in a table column ? It might take a lot of resource if I have to run it in a loop. Also how could I handle the tender block ?

nbk Over a year ago

you need to add another apply with tender , for the table you need another join before the open json

Jobin Over a year ago

That is solved thanks, having problem only in applying the same into the table which has got the JSON column.

Zhorov · Accepted Answer · 2023-01-11 14:54:31Z

1

The expected output defines the exact statement, but if you need to parse nested JSON content you may try a combination of:

OPENJSON() with explicit schema and the AS JSON modifier for nested JSON
additional APPLY operators for each nested level.

T-SQL:

SELECT
   j1.orderId, j1.openTime, j1.closeTime, j1.operatorId, j1.terminalId, j1.sessionId, j1.type, j1.memberId,
   j2.productGroupId, j2.productId, j2.quantity, j2.taxValue, j2.productValue, j2.priceBandId,
   j3.tenderId, j3.tenderValue
FROM stg_transactionsJson s
OUTER APPLY OPENJSON(s.stg_transactionsJson) WITH (
   orderId NVARCHAR(50) '$.orderId',
   openTime DATETIME '$.openTime',
   closeTime DATETIME '$.closeTime',
   operatorId NVARCHAR(50) '$.operatorId',
   terminalId NVARCHAR(50) '$.terminalId',
   sessionId NVARCHAR(50) '$.sessionId',
   products NVARCHAR(MAX) '$.products' AS JSON,
   tenders NVARCHAR(MAX) '$.tenders' AS JSON,
   type int '$.type',
   memberId int '$.memberId'
) j1
OUTER APPLY OPENJSON(j1.products) WITH (
   productGroupId NVARCHAR(50) '$.productGroupId',
   productId NVARCHAR(150) '$.productId',
   quantity NVARCHAR(50) '$.quantity',
   taxValue NVARCHAR(150) '$.taxValue',
   productValue NVARCHAR(50) '$.value',
   priceBandId NVARCHAR(150)'$.priceBandId'
) j2
OUTER APPLY OPENJSON(j1.tenders) WITH (
   tenderId NVARCHAR(150) '$.tenderId',
   tenderValue NVARCHAR(50) '$.value'
) j3

edited Jan 11, 2023 at 14:54

answered Jan 11, 2023 at 14:00

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

2 Comments

Jobin Over a year ago

Thank you so much. Creating another CTE with tenders solved the issue for tenders. How would it be possible to apply this at the table column level?

Zhorov Over a year ago

@Jobin, with an additional APPLY operator. The answer is updated.

Collectives™ on Stack Overflow

Flatten nested JSON data into SQL Server tables

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related