Query nested JSON data

Question

Post reply

Query nested JSON data

SQL_Surfer

SSCrazy Eights

Points: 8320
More actions
April 26, 2022 at 11:10 pm

#4022725

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 28, 2022 at 12:19 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

SQL_Surfer SSCrazy Eights Points: 8320 More actions · Answer 2

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

Attachments:

You must be logged in to view attached files.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

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

This reply was modified 2 years, 7 months ago by Mark Cowne.

____________________________________________________

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/61537

SQL_Surfer SSCrazy Eights Points: 8320 More actions · Answer 4

SQL_Surfer

SSCrazy Eights

Points: 8320

April 27, 2022 at 2:58 pm

#4023098

Excellent. Thank you both. This is awesome.