Flatten nested JSON data into SQL Server tables

Jobin
January 11, 2023
128 views
1 vote
2 Answers

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
}

Answers

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

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

Please signup or login to give your own answer.

Click here to cancel reply.