My goal is to dynamically parse a JSON string from a SQL column. I have tried many approaches, but am new to Power Query M.
I am storing a dynamic (I won't always know the structure, nor the names of the columns), and sometimes nested JSON, as a string in SQL. The JSON will look something like this:
{
"values": [{
"lid": "someguid1",
"jid": "someguid2",
"freq_gpsK0": 1.9261547623478617,
"freq_gpsK0_result": false,
"freq_accK0": 206.7747298420615,
"freq_accK0_result": false,
"freq_gyrK0": 206.73750946018041,
"freq_gyrK0_result": false,
"freq_camF0": 61.332985943102443,
"freq_camR0": 61.367104626493465,
"freq_camL0": 61.162392526147322,
"freq_camB0": 61.367104626493465,
"camFPS_result": false,
"longtrip": 644.808,
"longtrip_result": true,
"build": 26,
"build_result": true,
"max_gpsK0_v": 34.427,
"max_gpsK0_v_result": true,
"no_pii": true,
"numcams": 4,
"numcams_result": true,
"count_camF0_unordered": 0,
"count_camR0_unordered": 0,
"count_camL0_unordered": 0,
"count_camB0_unordered": 0,
"camsOrdered_result": true,
"freq_cbs0": 1583.5442488306596,
"freq_cbs1": 563.9663279611915,
"freq_cbs2": 1376.9215022146127,
"freq_cbs3": null,
"freq_cbs4": null,
"freq_cbs5": null,
"freq_cbs6": null,
"freq_cbs7": null,
"freq_cbs8": null,
"freq_cbs9": null,
"epoch": 99
}, {
"datetime": "2019-08-07T00:00:00",
"lid": "ad803d67-e9a4-4a67-87a5-a79c8a5b8f3e",
"jid": "63021e67-bf31-43f9-8f73-7e9c3e8522c4",
"datetime_result": true,
"length": 644801,
"length_result": true,
"inertial": 100.0,
"camera": 99.3,
"inertial_result": true,
"camera_result": true,
"accf0_inbounds": 100.0,
"accb0_inbounds": 100.0,
"accl0_inbounds": 100.0,
"accr0_inbounds": 100.0,
"acck0_inbounds": 100.0,
"gyrf0_inbounds": 100.0,
"gyrb0_inbounds": 100.0,
"gyrl0_inbounds": 100.0,
"gyrr0_inbounds": 100.0,
"gyrk0_inbounds": 100.0,
"camf0_inbounds": 100.0,
"camb0_inbounds": 100.0,
"caml0_inbounds": 100.0,
"camr0_inbounds": 100.0,
"camf0_inbounds_1sec": 100.0,
"camb0_inbounds_1sec": 99.9,
"caml0_inbounds_1sec": 99.9,
"camr0_inbounds_1sec": 100.0,
"camf0_inbounds_lateframes": 100.0,
"camb0_inbounds_lateframes": 100.0,
"caml0_inbounds_lateframes": 99.5,
"camr0_inbounds_lateframes": 100.0
}]
}We will consistently have a top level object, which holds a top level Array called "values", which holds JSON objects. The contents of "values" will be the dynamic part.
On the SQL side we're looking like:
| SomeIdField | attribute1 | EvalResults|
Where the SomeIdField is a unique GUID, and we have ~25 attributes, and then the EvalResults as shown in the snippet above. The EvalResults JSON object comes in each row, and I need the values parsed against each row.
I am able to expand out and retrieve the first object in the array simply by clicking through. But, Power BI drops the second object in the array when I do this and only stores values for the first object in the array. I don't see any way to access the second object, nor loop through to grab those contents.
Further, in the future I may have 1 - 15+ objects to deal with inside this Array.
My Power Query M look like this, when I do the expansion all the way through:
let
Source = Sql.Database("mysqlserver", "sqldb", [Query="SELECT * FROM mysqlview#(lf)"]),
#"Parsed JSON" = Table.TransformColumns(Source,{{"EvalResults", Json.Document}}),
#"Expanded EvalResults" = Table.ExpandRecordColumn(#"Parsed JSON", "EvalResults", {"values"}, {"EvalResults.values"}),
#"Expanded EvalResults.values" = Table.ExpandListColumn(#"Expanded EvalResults", "EvalResults.values"),
#"Expanded EvalResults.values1" = Table.ExpandRecordColumn(#"Expanded EvalResults.values", "EvalResults.values", {"lid", "jid", "freq_gpsK0", "freq_gpsK0_result", "freq_accK0", "freq_accK0_result", "freq_gyrK0", "freq_gyrK0_result", "freq_camF0", "freq_camR0", "freq_camL0", "freq_camB0", "camFPS_result", "longtrip", "longtrip_result", "build", "build_result", "max_gpsK0_v", "max_gpsK0_v_result", "no_pii", "numcams", "numcams_result", "count_camF0_unordered", "count_camR0_unordered", "count_camL0_unordered", "count_camB0_unordered", "camsOrdered_result", "epoch"}, {"EvalResults.values.lid", "EvalResults.values.jid", "EvalResults.values.freq_gpsK0", "EvalResults.values.freq_gpsK0_result", "EvalResults.values.freq_accK0", "EvalResults.values.freq_accK0_result", "EvalResults.values.freq_gyrK0", "EvalResults.values.freq_gyrK0_result", "EvalResults.values.freq_camF0", "EvalResults.values.freq_camR0", "EvalResults.values.freq_camL0", "EvalResults.values.freq_camB0", "EvalResults.values.camFPS_result", "EvalResults.values.longtrip", "EvalResults.values.longtrip_result", "EvalResults.values.build", "EvalResults.values.build_result", "EvalResults.values.max_gpsK0_v", "EvalResults.values.max_gpsK0_v_result", "EvalResults.values.no_pii", "EvalResults.values.numcams", "EvalResults.values.numcams_result", "EvalResults.values.count_camF0_unordered", "EvalResults.values.count_camR0_unordered", "EvalResults.values.count_camL0_unordered", "EvalResults.values.count_camB0_unordered", "EvalResults.values.camsOrdered_result", "EvalResults.values.epoch"})
in
#"Expanded EvalResults.values1"And this would work and be fine if I never had another JSON object that I needed data from.
If I were doing this in something like C#, Java etc, I would simply loop through the Array of Objects, grab the values, and be on my way.
Is this feasible with Power BI / Power Query M?
Is there something simple I am missing?
Thank you!
