how to parse json into rows instead of columns?

Question

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

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

What what are your expected results here? Also, what you have given us doesn't appear to work: db<>fiddle. — Thom A
– Thom A ♦, Commented Dec 30, 2019 at 12:14
its like this guys, just remove the ... and the comma at the end. , let me update it to working. the payload is far bigger. — The Fool
– The Fool, Commented Dec 30, 2019 at 12:15
That doesn't fix the issue, @TheFool , like I showed you in the fiddle... — Thom A
– Thom A ♦, Commented Dec 30, 2019 at 12:16
because you need to remove the semicolons as well if you dont use ssms. the variable is not declared anymore after semicolon — The Fool
– The Fool, Commented Dec 30, 2019 at 12:19

Zhorov · Accepted Answer · 2019-12-30 12:48:04Z

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

Gordon Linoff · Accepted Answer · 2019-12-30 12:16:09Z

1

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

answered Dec 30, 2019 at 12:16

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

Thom A · Accepted Answer · 2019-12-30 12:20:18Z

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);

Collectives™ on Stack Overflow

how to parse json into rows instead of columns?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related