Forum Discussion
Solved
Parse Dynamic JSON from SQL Column
I've posted this earlier to Desktop, but apparently that post didn't take, so forgive me if this shows up twice!
My goal is to parse a JSON string column that is being served up to power bi via a SQL source column. I can successfully parse this column by clicking through and expanding, but it only parses the first object found in my JSON array. It does not parse the second object.
I have a SQL View as my source with roughly 30 columns in it. The columns for each row are a Unique ID, 28 other attributes, and a JSON string which is Dynamic in that I won't know the contents, and it could be nested.
The SQL layout looks like:
LID | ~28 Attributes | EvalResults
EvalResults is the JSON in question, which looks like:
{
"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
}]
}I will always have the "values" array, but anything after that is up in the air from nothing in the Array to 15 object in the array. As you can see, I also won't know the names of the keys in the JSON object.
When I successfully parse out the first object in this array, the Power Query M looks like this:
let
Source = Sql.Database("mysqlserver", "mydbname", [Query="SELECT * FROM myViewName#(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"
This would be OK if I only ever needed to grab one object from the array, but it fails at getting down to the second object.
I have tried a lot of finessing with Power Query M, but I am new to it.
Is this possible to achieve? I would be open to either one big table with all of the columns in it, or sub tables under each row (as long as the performance wasn't an issue). Whatever allows me to get at the data!
Thank you in advance!
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.
10 Replies
- ImkeF
Community Champion
Hi Naesevol
not sure if my understanding is correct, but please check what this code does. No hardcoded Field/Column-names in there:
let Source = "[{#(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)}, #(cr)#(lf){#(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)}#(cr)#(lf)]", #"Parsed JSON" = Json.Document(Source), #"Converted to Table" = Table.FromList(#"Parsed JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"values"}, {"values"}), #"Added Index" = Table.AddIndexColumn(#"Expanded Column1", "Index", 1, 1), ListOfRecordsToTable = Table.AddColumn(#"Added Index", "Custom", each Record.ToTable(Record.Combine([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"Hi ImkeF - this definitely parses both objects in the values array!
It looks like adding an index was probably the thing I was missing. Taking it to a table, expanding, and pivoting back is clever.
That said, it looks like you've changed the JSON structure a little, and you've made the top level an array as opposed to an object, like:
[{ "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 }] }, { "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 }] } ]Is this necessary? If so, I think I can alter my structure to always have an array at the top level.
I will try to apply this to my actual SQL Source JSON Column and report back with the findings.
Thanks!
- ImkeF
Hi Naesevol
no, there's no need to adjust to that structure. I just assumed that there must be more than one element there.
The latest JSON you've posted looks exactly like mine. So if you actually have a different structure for multiple objects, please post and I'll adjust the code.
