CODEWITHSUNDEEP
azure-data-lakeu-sql
David
David

Reputation: 35

U-sql call data in json array

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();

Upvotes: 1

Views: 371

Answers (1)

Michael Rys
Michael Rys

Reputation: 6684

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();

Upvotes: 2

Related Questions