Place key-value pairs of a JSON object into separate columns

user_2625338 0
Viewed 1914

I have a JSON object which I have declared in SQL Server like so

DECLARE @json NVARCHAR(MAX)

SET @json=N'{"success": "True",
 "timestamp": "1618498386",
 "base": "EUR",
 "date": "2021-04-15",
 "rates": {"AED": "4.395136",
  "AFN": "92.798516",
  "ALL": "123.01053",
  "AMD": "623.68381",
  "UAH": "33.449108",
  "UGX": "4330.311325",
  "USD": "1.196623"}}';

SELECT * FROM  
 OPENJSON ( @json )  
WITH (   
              AED   varchar(200) '$.rates.AED',
              USD   varchar(200) '$.rates.USD',  
              Date     datetime     '$.date',  
              BASE_CURR varchar(200) '$.base'
 )

From the above JSON object I am trying to build a structured table as follows

base date CURR_KEY CURR_VALUE
EUR 2021-04-15 AED 4.395136
EUR 2021-04-15 AFN 92.798516
EUR 2021-04-15 ALL 123.01053
EUR 2021-04-15 AMD 623.68381
EUR 2021-04-15 UAH 33.449108
EUR 2021-04-15 UGX 4330.311325
EUR 2021-04-15 USD 1.196623

I referred the relevant Option 2 in Microsoft docs which shows how to place keys of a JSON object as column names and values as row values (UPDATE: refer the query above). As you can see from the above table, my requirements are to place both keys and values in two separate columns.

sql-server sql json
2 Answers

You could use OPENJSON or JSON_VALUE to get the root object.

Then you need OPENJSON without a schema to get key/value pairs of '$.rates'.

SELECT
    j.base,
    j.date,
    CAST(rates.[key] AS char(3)) as CURR_KEY,
    CAST(rates.value AS decimal(15,6)) as CURR_VALUE
FROM OPENJSON(@json)
     WITH ([base] char(3),
           [date] date,
           rates nvarchar(MAX) AS JSON) j
     CROSS APPLY OPENJSON(j.Rates) rates;

SELECT
    JSON_VALUE(@json, '$.base') AS base,
    JSON_VALUE(@json, '$.date') AS date,
    cast(rates.[key] as char(3)) as CURR_KEY,
    cast(rates.value as decimal(15,6)) as CURR_VALUE
FROM OPENJSON(@json, '$.rates') rates;
user_148689970

One method is using 2 calls to OPENJSON:

DECLARE @json NVARCHAR(MAX)

SET @json=N'{"success": "True",
 "timestamp": "1618498386",
 "base": "EUR",
 "date": "2021-04-15",
 "rates": {"AED": "4.395136",
  "AFN": "92.798516",
  "ALL": "123.01053",
  "AMD": "623.68381",
  "UAH": "33.449108",
  "UGX": "4330.311325",
  "USD": "1.196623"}}';

 
SELECT *
FROM OPENJSON(@json)
     WITH (BASE_CURR varchar(200) '$.base',
           [Date] date,
           rates nvarchar(MAX) AS JSON) OJ
     CROSS APPLY OPENJSON(OJ.Rates)
                 WITH (AED decimal(15,6),
                       USD decimal(15,6)) R;
user_20299830
Related