Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Hi @Naesevol ,
You can refer to the links:
https://sqlwithmanoj.com/2015/11/01/reading-json-string-with-nested-elements-sql-server-2016-part-3/
(Please understand that this link is provided “AS IS” with no warranties or guarantees of content changes, and confers no rights.)
If you still have this issue for Power BI, you'd better create a support ticket in Power BI Support to get further help.
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |