April 26, 2022 at 11:10 pm
table structure
create table dbo.commodityIndex(sucess varchar(10), [timestamp] varchar(100), [date] date, base varchar(10), rates decimal(10,2), unit varchar(10) )
JSON data is as follows:
How do I query the nested piece in rates?
declare @json1 VARCHAR(MAX);
set @json1 = '{"success": true, "timestamp": 1650998580, "date": "2022-04-26", "base": "USD", "rates": {"USD": 1, "XAG": 0.042738743354897, "XAU": 0.0005276742457391, "XPD": 0.00047847992351816, "XPT": 0.0010886942328618, "XRH": 5.4658469945355e-05}, "unit": "per ounce"}'
SELECT success, [timestamp], [date],base,rates,unit
FROM OPENJSON(@json1)
WITH (
success NVARCHAR(500) '$.success',
timestamp NVARCHAR(500) '$.timestamp',
base NVARCHAR(500) '$.base',
date NVARCHAR(500) '$.date',
rates VARCHAR(100) '$.rates',
unit VARCHAR(100) '$.unit'
)
April 27, 2022 at 9:05 am
Try this:
SELECT
q1.success
, q1.timestamp
, q1.date
, q1.base
, q1.rates
, q1.unit
, q2.USD
, q2.XAG
FROM
OPENJSON(@json1)
WITH
(
success NVARCHAR(500)
, timestamp NVARCHAR(500)
, base NVARCHAR(500)
, date NVARCHAR(500)
, rates NVARCHAR(MAX) AS JSON
, unit VARCHAR(100)
) q1
CROSS APPLY
OPENJSON(q1.rates)
WITH (USD VARCHAR(50), XAG VARCHAR(50)) q2;
April 27, 2022 at 11:35 am
Thanks Phil. This defintiely looks much better.
All the rates are now coming in one column. (image attached).
What I want is to look like this. (Ignore firs USD:1 from the rates)
sucess timestamp date base commodity, rates units
true 1650998580 2022-04-26 usd XAG 0.042738743354897 per ounce
true 1650998580 2022-04-26 usd XPD 0.00047847992351816 per ounce
true 1650998580 2022-04-26 usd XPT 0.0010886942328618 per ounce
true 1650998580 2022-04-26 usd XRH 5.4658469945355e-05 per ounce
April 27, 2022 at 1:23 pm
Small tweak to Phils code should do it
SELECT
q1.success
, q1.timestamp
, q1.date
, q1.base
, q1.unit
, q2.[key] AS Currency
, q2.value AS Rate
FROM
OPENJSON(@json1)
WITH
(
success NVARCHAR(500)
, timestamp NVARCHAR(500)
, base NVARCHAR(500)
, date NVARCHAR(500)
, rates NVARCHAR(MAX) AS JSON
, unit VARCHAR(100)
) q1
CROSS APPLY
OPENJSON(q1.rates) q2
WHERE q2.[key] <> 'USD';
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537April 27, 2022 at 2:58 pm
Excellent. Thank you both. This is awesome.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy