U-sql call data in json array

Question

I have browsed the web and forum to download the data from the file json, but my script does not work. I have a problem with downloading the list of objects of rates. Can someone please help? I can not find fault.

{"table":"C","no":"195/C/NBP/2016","tradingDate":"2016-10-06","effectiveDate":"2016-10-07","rates":
[
{"currency":"dolar amerykaĹ„ski","code":"USD","bid":3.8011,"ask":3.8779},
{"currency":"dolar australijski","code":"AUD","bid":2.8768,"ask":2.935},
{"currency":"dolar kanadyjski","code":"CAD","bid":2.8759,"ask":2.9339},
{"currency":"euro","code":"EUR","bid":4.2493,"ask":4.3351},
{"currency":"forint (WÄ™gry)","code":"HUF","bid":0.013927,"ask":0.014209},
{"currency":"frank szwajcarski","code":"CHF","bid":3.8822,"ask":3.9606},
{"currency":"funt szterling","code":"GBP","bid":4.8053,"ask":4.9023},
{"currency":"jen (Japonia)","code":"JPY","bid":0.036558,"ask":0.037296},
{"currency":"korona czeska","code":"CZK","bid":0.1573,"ask":0.1605},
{"currency":"korona duĹ„ska","code":"DKK","bid":0.571,"ask":0.5826},
{"currency":"korona norweska","code":"NOK","bid":0.473,"ask":0.4826},
{"currency":"korona szwedzka","code":"SEK","bid":0.4408,"ask":0.4498},
{"currency":"SDR (MFW)","code":"XDR","bid":5.3142,"ask":5.4216}
],
"EventProcessedUtcTime":"2016-10-09T10:48:41.6338718Z","PartitionId":1,"EventEnqueuedUtcTime":"2016-10-09T10:48:42.6170000Z"}

This is my script in sql.

@trial =
    EXTRACT jsonString string
    FROM @"adl://kamilsepin.azuredatalakestore.net/ExchangeRates/2016/10/09/10_0_c60d8b8895b047c896ce67d19df3cdb2.json"
    USING Extractors.Text(delimiter:'\b', quoting:false);

@json =
    SELECT Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(jsonString) AS rec
    FROM @trial;

@columnized =
    SELECT
            rec["table"]AS table,
            rec["no"]AS no,
            rec["tradingDate"]AS tradingDate,
            rec["effectiveDate"]AS effectiveDate,
            rec["rates"]AS rates
    FROM @json;
@rateslist =
    SELECT
        table, no, tradingDate, effectiveDate,
        Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(rates) AS recl
        FROM @columnized;
@selectrates =
    SELECT
    recl["currency"]AS currency,
    recl["code"]AS code,
    recl["bid"]AS bid,
    recl["ask"]AS ask
    FROM @rateslist;

OUTPUT @selectrates 
    TO "adl://kamilsepin.azuredatalakestore.net/datastreamanalitics/ExchangeRates.tsv"
USING Outputters.Tsv();

Michael Rys · Accepted Answer · 2016-10-10 19:23:31Z

2

You need to look at the structure of your JSON and identify, what constitutes your first path inside your JSON that you want to map to correlated rows. In your case, you are really only interested in the array in rates where you want one row per array item.

Thus, you use the JSONExtractor with a JSONPath that gives you one row per array element (e.g., rates[*]) and then project each of its fields.

Here is the code (with slightly changed paths):

REFERENCE ASSEMBLY JSONBlog.[Newtonsoft.Json];
REFERENCE ASSEMBLY JSONBlog.[Microsoft.Analytics.Samples.Formats]; 

@selectrates =
    EXTRACT currency string, code string, bid decimal, ask decimal
    FROM @"/Temp/rates.json"
    USING new Microsoft.Analytics.Samples.Formats.Json.JsonExtractor("rates[*]");

OUTPUT @selectrates 
TO "/Temp/ExchangeRates.tsv"
USING Outputters.Tsv();

answered Oct 10, 2016 at 19:23

Michael Rys

6,68417 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

U-sql call data in json array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related