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.
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!