Query nested JSON data

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

    )

     

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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.
  • 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 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
  • Excellent. Thank you both. This is awesome.

  • 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