CODEWITHSUNDEEP
sqlsql-servert-sql
The Fool
The Fool

Reputation: 20597

how to parse json into rows instead of columns?

I am trying to update a table from a JSON object. The table has a structure like

enter image description here

In this table, each quote would be the new single row with the quote key as the value for the column ABBR.

Based on the examples I found, I was able to do below, but now I am stuck on how to update the table there. It creates a new column for each quote key.

DECLARE @fx_rates_json NVARCHAR(MAX)

SET @fx_rates_json = N'{
    "date": "2019-12-02",
    "quotes": {
        "USDARS": 3.673197,
        "USDAUD": 78.79768,
        "USDBGN": 110.795056
    }
}'

SELECT jsonpayload.*
FROM OPENJSON (@fx_rates_json, N'$')
  WITH (
    Date DATETIME N'$.date',
    ARS float N'$.quotes.USDARS',
    AUD float N'$.quotes.USDAUD',
    BGN float N'$.quotes.USDBGN'
  ) AS jsonpayload

Upvotes: 0

Views: 382

Answers (3)

Zhorov
Zhorov

Reputation: 30003

If I understand you correctly and you want to parse this JSON input and get result as rows, the next statement may help. You need to use OPENJSON() with default schema (without the WITH clause). In this case the result is a table with columns key, value and type.

JSON:

DECLARE @fx_rates_json NVARCHAR(MAX);
SET @fx_rates_json = N'{
    "date": "2019-12-02",
    "quotes": {
        "USDAED": 3.673197,
        "USDAFN": 78.79768,
        "USDALL": 110.795056
    }
}';

Statement:

SELECT 
   REPLACE(jsonpayload.[key], N'USD', N'') AS ABBREVIATION,
   jsonpayload.[value] AS CONVERSION,
   JSON_VALUE(@fx_rates_json, N'$.date') AS [DATE]
FROM OPENJSON (@fx_rates_json, N'$.quotes') jsonpayload

Result:

--------------------------------------
ABBREVIATION    CONVERSION  DATE
--------------------------------------
AED             3.673197    2019-12-02
AFN             78.79768    2019-12-02
ALL             110.795056  2019-12-02

Upvotes: 1

Thom A
Thom A

Reputation: 96016

I think what you are after is this:

DECLARE @fx_rates_json nvarchar(MAX);

SET @fx_rates_json = N'{
    "date": "2019-12-02",
    "quotes": {
        "USDAED": 3.673197,
        "USDAFN": 78.79768,
        "USDALL": 110.795056
    }
}';

SELECT payload.date,
       V.Currency,
       V.value
FROM OPENJSON(@fx_rates_json)
         WITH ([date] date,
               quotes nvarchar(MAX) AS JSON) payload
     CROSS APPLY OPENJSON(payload.quotes)
                     WITH(AED float N'$.USDAED',
                          AFN float N'$.USDAFN',
                          [ALL] float N'$.USDALL') quotes
     CROSS APPLY (VALUES('ARS',quotes.AED),
                        ('AUG',quotes.AFN),
                        ('BGN',quotes.[ALL])) V(Currency,value);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271111

You can use joins in an update:

update t
    set conversion = v.value
from toupdate t cross join
     openjson (@fx_rates_json, N'$')
     with (Date DATETIME N'$.date',
           ARS float N'$.quotes.USDARS',
           AUD float N'$.quotes.USDAUD',
           BGN float N'$.quotes.USDBGN',
     ) j cross apply
     (values ('ARS', j.ARS), ('AUD', j.AUD), ('BGN', j.BGN)
     ) v(abbreviation, value)
where t.abbreviation = v.abbrevation

Upvotes: 1

Related Questions