Post reply

Query nested JSON data

  • 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 27, 2022 at 9:05 am

    #4022900

    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.

  • April 27, 2022 at 11:35 am

    #4022979

    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.

  • April 27, 2022 at 1:23 pm

    #4023050

    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 1 year, 12 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

  • April 27, 2022 at 2:58 pm

    #4023098

    Excellent. Thank you both. This is awesome.

  • 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