T-SQL query that takes json columnar data and displays it in row format-CodePudding

In an Azure SQL database, I have a column that is storing json data.

SELECT a.Response 
FROM [dbo].[Api] a

The result of this query is json in columnar format

{
    "PRODUCT": {
        "0": "a1",
        "1": "a2",
        "2": "a3",
        "3": "a4"
    },
    "STOCK": {
        "0": 3.0,
        "1": 3.0,
        "2": 0.5,
        "3": 6.0
    },
    "SALES": {
        "0": 2487.0,
        "1": 1841.0,
        "2": 391.0,
        "3": 2732.0
    }
}

What I'd like to do is to have a query which displays the above data in SQL row format

PRODUCT | STOCK | SALES 
-------- ------- ------
 a1     | 3.0   | 2487.0
 a2     | 3.0   | 1841.0,
 a3     | 0.5   | 391.0,
 a1     | 6.0   | 2732.0

CodePudding user response：

Assuming that the value of the key is the relation, and all keys have a value in all 3 JSON objects, one method would be to use a few calls to OPENJSON and define the relationship in the WHERE:

SELECT P.[value] aS Product,
       St.[value] AS Stock,
       Sa.[value] AS Sales
FROM [dbo].[Api] a
     CROSS APPLY OPENJSON (a.Response, '$.PRODUCT') P
     CROSS APPLY OPENJSON (a.Response, '$.STOCK') St
     CROSS APPLY OPENJSON (a.Response, '$.SALES') Sa
WHERE P.[key] = St.[Key]
  AND St.[Key] = Sa.[Key];

db<>fiddle