Forum Discussion
Parse Dynamic JSON from SQL Column
Hi Naesevol
this gets us back to my original solution where I assumed that there must probably more than one JSON object involved. Im just borrowing the last lines of code and combine them with your scenario:
let Source = Sql.Database("mysqlserver", "mysqldb", [Query="SELECT * FROM mySQLView#(lf)"]), #"Filtered Rows" = Table.SelectRows(Source, each ([EvalResults] <> null)), #"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"AcquireTime", Order.Descending}}), #"Parsed JSON" = Table.TransformColumns(#"Sorted Rows1",{{"EvalResults", Json.Document}}), #"Expanded EvalResults" = Table.ExpandRecordColumn(#"Parsed JSON", "EvalResults", {"values"}, {"EvalResults.values"}), ListOfRecordsToTable = Table.AddColumn(#"Expanded EvalResults", "Custom", each Record.ToTable(Record.Combine([EvalResults.values]))), #"Expanded Custom" = Table.ExpandTableColumn(ListOfRecordsToTable, "Custom", {"Name", "Value"}, {"Name", "Value"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"values"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Name]), "Name", "Value") in #"Pivoted Column"Of course here could be typos or name mismatches, as I couldn't test your code.
But the important thing is NOT to expand the list column (as this will cause the dups), but instead add the column "Custom" instead.
No prob!
If it's just one object, the code is even simpler:
let
Query2 = "{#(cr)#(lf)#(tab)""values"": [{#(cr)#(lf)#(tab)#(tab)""lid"": ""someguid1"",#(cr)#(lf)#(tab)#(tab)""jid"": ""someguid2"",#(cr)#(lf)#(tab)#(tab)""freq_gpsK0"": 1.9261547623478617,#(cr)#(lf)#(tab)#(tab)""freq_gpsK0_result"": false,#(cr)#(lf)#(tab)#(tab)""freq_accK0"": 206.7747298420615,#(cr)#(lf)#(tab)#(tab)""freq_accK0_result"": false,#(cr)#(lf)#(tab)#(tab)""freq_gyrK0"": 206.73750946018041,#(cr)#(lf)#(tab)#(tab)""freq_gyrK0_result"": false,#(cr)#(lf)#(tab)#(tab)""freq_camF0"": 61.332985943102443,#(cr)#(lf)#(tab)#(tab)""freq_camR0"": 61.367104626493465,#(cr)#(lf)#(tab)#(tab)""freq_camL0"": 61.162392526147322,#(cr)#(lf)#(tab)#(tab)""freq_camB0"": 61.367104626493465,#(cr)#(lf)#(tab)#(tab)""camFPS_result"": false,#(cr)#(lf)#(tab)#(tab)""longtrip"": 644.808,#(cr)#(lf)#(tab)#(tab)""longtrip_result"": true,#(cr)#(lf)#(tab)#(tab)""build"": 26,#(cr)#(lf)#(tab)#(tab)""build_result"": true,#(cr)#(lf)#(tab)#(tab)""max_gpsK0_v"": 34.427,#(cr)#(lf)#(tab)#(tab)""max_gpsK0_v_result"": true,#(cr)#(lf)#(tab)#(tab)""no_pii"": true,#(cr)#(lf)#(tab)#(tab)""numcams"": 4,#(cr)#(lf)#(tab)#(tab)""numcams_result"": true,#(cr)#(lf)#(tab)#(tab)""count_camF0_unordered"": 0,#(cr)#(lf)#(tab)#(tab)""count_camR0_unordered"": 0,#(cr)#(lf)#(tab)#(tab)""count_camL0_unordered"": 0,#(cr)#(lf)#(tab)#(tab)""count_camB0_unordered"": 0,#(cr)#(lf)#(tab)#(tab)""camsOrdered_result"": true,#(cr)#(lf)#(tab)#(tab)""freq_cbs0"": 1583.5442488306596,#(cr)#(lf)#(tab)#(tab)""freq_cbs1"": 563.9663279611915,#(cr)#(lf)#(tab)#(tab)""freq_cbs2"": 1376.9215022146127,#(cr)#(lf)#(tab)#(tab)""freq_cbs3"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs4"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs5"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs6"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs7"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs8"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs9"": null,#(cr)#(lf)#(tab)#(tab)""epoch"": 99#(cr)#(lf)#(tab)}, {#(cr)#(lf)#(tab)#(tab)""datetime"": ""2019-08-07T00:00:00"",#(cr)#(lf)#(tab)#(tab)""lid"": ""ad803d67-e9a4-4a67-87a5-a79c8a5b8f3e"",#(cr)#(lf)#(tab)#(tab)""jid"": ""63021e67-bf31-43f9-8f73-7e9c3e8522c4"",#(cr)#(lf)#(tab)#(tab)""datetime_result"": true,#(cr)#(lf)#(tab)#(tab)""length"": 644801,#(cr)#(lf)#(tab)#(tab)""length_result"": true,#(cr)#(lf)#(tab)#(tab)""inertial"": 100.0,#(cr)#(lf)#(tab)#(tab)""camera"": 99.3,#(cr)#(lf)#(tab)#(tab)""inertial_result"": true,#(cr)#(lf)#(tab)#(tab)""camera_result"": true,#(cr)#(lf)#(tab)#(tab)""accf0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""accb0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""accl0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""accr0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""acck0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrf0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrb0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrl0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrr0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrk0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""camf0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""camb0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""caml0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""camr0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""camf0_inbounds_1sec"": 100.0,#(cr)#(lf)#(tab)#(tab)""camb0_inbounds_1sec"": 99.9,#(cr)#(lf)#(tab)#(tab)""caml0_inbounds_1sec"": 99.9,#(cr)#(lf)#(tab)#(tab)""camr0_inbounds_1sec"": 100.0,#(cr)#(lf)#(tab)#(tab)""camf0_inbounds_lateframes"": 100.0,#(cr)#(lf)#(tab)#(tab)""camb0_inbounds_lateframes"": 100.0,#(cr)#(lf)#(tab)#(tab)""caml0_inbounds_lateframes"": 99.5,#(cr)#(lf)#(tab)#(tab)""camr0_inbounds_lateframes"": 100.0#(cr)#(lf)#(tab)}]#(cr)#(lf)}",
#"Parsed JSON" = Json.Document(Query2),
values = #"Parsed JSON"[values],
Custom1 = Record.Combine(values),
Custom2 = Table.FromRecords({Custom1})
in
Custom2
Hm - there appears to be a bug whenever I edit a reply to a form post, my post disappears, so I am resubmitting my reply.
Your most recent reply gets me parsed down properly, but it results in a new table which drops all of my previous columns, and only keeps the JSON column as the new table.
I think maybe I was not clear enough on the ask originally, my apologies:
I need to be able to loop through each SQL Row, parse the JSON column, and store the resulting Keys from the JSON as new columns on the SQL Source Table (or store the JSON key/values as a new table that relates to each row).
A simple example below.
Take my source:
| lid | sqlcol1 | sqlcol 2| EvalResults |
1 yyy iii json1
2 xxx bbb json2
Where json1 is:
{
"values": [{
"aaa": 1.92,
"bbb": false
}]
}
Where json2 is:
{
"values": [{
"aaa": 2.00,
"bbb": true
}, {
"xxx": "2.2",
"yyy": true
}]
}
I would want my resulting table to look like:
| lid | sqlcol1 | sqlcol 2| EvalResults.aaa |EvalResults.bbb|EvalResults.xxx|EvalResults.yyy
1 yyy iii 1.92 false null null
2 xxx bbb 2.00 true 2.2 true
Is this possible? I imagine I will need to loop through each row, but am not familiar enough with Power Query M to be certain that is correct, or even doable.
Thank you so much for the help so far!
Updating for any future seekers of knowledge:
My first SQL row had a JSON column that only contained an array which held a single object.
When I sorted my SQL rows by most recently added, I had a JSON column that contained an array which held more than one object.
Power BI then recgonized that there were multiple objects to Parse. Here is my resulting Power Query M when it appropriately recognized the JSON column:
let Source = Sql.Database("mysqlserver", "mysqldb", [Query="SELECT * FROM mySQLView#(lf)"]), #"Filtered Rows" = Table.SelectRows(Source, each ([EvalResults] <> null)), #"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"AcquireTime", Order.Descending}}), #"Parsed JSON" = Table.TransformColumns(#"Sorted Rows1",{{"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", "freq_cbs0", "freq_cbs1", "freq_cbs2", "freq_cbs3", "freq_cbs4", "freq_cbs5", "freq_cbs6", "freq_cbs7", "freq_cbs8", "freq_cbs9", "epoch", "datetime", "datetime_result", "length", "length_result", "inertial", "camera", "inertial_result", "camera_result", "accf0_inbounds", "accb0_inbounds", "accl0_inbounds", "accr0_inbounds", "acck0_inbounds", "gyrf0_inbounds", "gyrb0_inbounds", "gyrl0_inbounds", "gyrr0_inbounds", "gyrk0_inbounds", "camf0_inbounds", "camb0_inbounds", "caml0_inbounds", "camr0_inbounds", "camf0_inbounds_1sec", "camb0_inbounds_1sec", "caml0_inbounds_1sec", "camr0_inbounds_1sec", "camf0_inbounds_lateframes", "camb0_inbounds_lateframes", "caml0_inbounds_lateframes", "camr0_inbounds_lateframes", "ReasonForRecording", "FingerPrint"}, {"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.freq_cbs0", "EvalResults.values.freq_cbs1", "EvalResults.values.freq_cbs2", "EvalResults.values.freq_cbs3", "EvalResults.values.freq_cbs4", "EvalResults.values.freq_cbs5", "EvalResults.values.freq_cbs6", "EvalResults.values.freq_cbs7", "EvalResults.values.freq_cbs8", "EvalResults.values.freq_cbs9", "EvalResults.values.epoch", "EvalResults.values.datetime", "EvalResults.values.datetime_result", "EvalResults.values.length", "EvalResults.values.length_result", "EvalResults.values.inertial", "EvalResults.values.camera", "EvalResults.values.inertial_result", "EvalResults.values.camera_result", "EvalResults.values.accf0_inbounds", "EvalResults.values.accb0_inbounds", "EvalResults.values.accl0_inbounds", "EvalResults.values.accr0_inbounds", "EvalResults.values.acck0_inbounds", "EvalResults.values.gyrf0_inbounds", "EvalResults.values.gyrb0_inbounds", "EvalResults.values.gyrl0_inbounds", "EvalResults.values.gyrr0_inbounds", "EvalResults.values.gyrk0_inbounds", "EvalResults.values.camf0_inbounds", "EvalResults.values.camb0_inbounds", "EvalResults.values.caml0_inbounds", "EvalResults.values.camr0_inbounds", "EvalResults.values.camf0_inbounds_1sec", "EvalResults.values.camb0_inbounds_1sec", "EvalResults.values.caml0_inbounds_1sec", "EvalResults.values.camr0_inbounds_1sec", "EvalResults.values.camf0_inbounds_lateframes", "EvalResults.values.camb0_inbounds_lateframes", "EvalResults.values.caml0_inbounds_lateframes", "EvalResults.values.camr0_inbounds_lateframes", "EvalResults.values.ReasonForRecording", "EvalResults.values.FingerPrint"}) in #"Expanded EvalResults.values1"I fear this is not very scalable, since the JSON can, and will shift in the future.. but maybe it will help someone looking for a solve for a smaller data set.
Now I am fighting with a new issue. Each row is being duplicated. I'll need to figure out a way to join the rows on LID, so that I don't have twice the number of records (or thrice if I happened to ever have three objects to parse).
Will report back when I figure out a path forward without dupes.
Hi Naesevol
this gets us back to my original solution where I assumed that there must probably more than one JSON object involved. Im just borrowing the last lines of code and combine them with your scenario:
let Source = Sql.Database("mysqlserver", "mysqldb", [Query="SELECT * FROM mySQLView#(lf)"]), #"Filtered Rows" = Table.SelectRows(Source, each ([EvalResults] <> null)), #"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"AcquireTime", Order.Descending}}), #"Parsed JSON" = Table.TransformColumns(#"Sorted Rows1",{{"EvalResults", Json.Document}}), #"Expanded EvalResults" = Table.ExpandRecordColumn(#"Parsed JSON", "EvalResults", {"values"}, {"EvalResults.values"}), ListOfRecordsToTable = Table.AddColumn(#"Expanded EvalResults", "Custom", each Record.ToTable(Record.Combine([EvalResults.values]))), #"Expanded Custom" = Table.ExpandTableColumn(ListOfRecordsToTable, "Custom", {"Name", "Value"}, {"Name", "Value"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"values"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Name]), "Name", "Value") in #"Pivoted Column"Of course here could be typos or name mismatches, as I couldn't test your code.
But the important thing is NOT to expand the list column (as this will cause the dups), but instead add the column "Custom" instead.
IMK you're a hero! Just a slight tweak for my specific names:
let Source = Sql.Database("mysqldb, "mysqlserver", [Query="SELECT * FROM MySqlView#(lf)"]), #"Filtered Rows" = Table.SelectRows(Source, each ([EvalResults] <> null)), #"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"AcquireTime", Order.Descending}}), #"Parsed JSON" = Table.TransformColumns(#"Sorted Rows1",{{"EvalResults", Json.Document}}), #"Expanded EvalResults" = Table.ExpandRecordColumn(#"Parsed JSON", "EvalResults", {"values"}, {"EvalResults.values"}), ListOfRecordsToTable = Table.AddColumn(#"Expanded EvalResults", "Custom", each Record.ToTable(Record.Combine([EvalResults.values]))), #"Expanded Custom" = Table.ExpandTableColumn(ListOfRecordsToTable, "Custom", {"Name", "Value"}, {"Name", "Value"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"EvalResults.values"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Name]), "Name", "Value"), #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"AcquireTime", Order.Descending}}) in #"Sorted Rows"The key was indeed:
But the important thing is NOT to expand the list column (as this will cause the dups), but instead add the column "Custom" instead.
And then of course the removal of the EvalResults.values (original expanded column) and the pivot back.
Really appreciate it!