Question 110322 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Flatten nested JSON data into SQL Server tables

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
}
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search