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
>Solution :
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];