Forum Discussion
BIanon
Helper V
Helper VExtracting key/value pairs from json
Hello Community, I am reading some data from a table in Big Query and one of the columns is a nested column which is pulled in to PQ in JSON format like this: {"v":[{"v":{"f":[{"v":"MANUF...
Once you get down to the last level of list, which has the key as one record and the value as another, you can create 2 new columns, one to grab each record, then expand the records out.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wqo5RKotRsoqG0kAqDYkbo+Tr6Bfq5ugcEhrkGhSjVKsDl3AJ9fWNVHAP8g8NAIrH1sKlMIzwDwjy9HNx9Ql2DfZx9HNBMcU1NMg/wJGAAT6HZwSHHW5zD0HR6uYINJGATk+/YNegEE9UO10sHI2cXV1Ndc2NzC10TQzcDHQd3ZxddI0MHS2djF0MXA2NnCDmxtYqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Parsed JSON" = Table.TransformColumns(#"Changed Type",{},Json.Document), #"Expanded Column1" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1", {"v"}, {"Column1.v"}), #"Expanded Column1.v" = Table.ExpandListColumn(#"Expanded Column1", "Column1.v"), #"Expanded Column1.v1" = Table.ExpandRecordColumn(#"Expanded Column1.v", "Column1.v", {"v"}, {"Column1.v.v"}), #"Expanded Column1.v.v" = Table.ExpandRecordColumn(#"Expanded Column1.v1", "Column1.v.v", {"f"}, {"Column1.v.v.f"}), #"Added Custom" = Table.AddColumn(#"Expanded Column1.v.v", "key", each [Column1.v.v.f]{0}), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Value", each [Column1.v.v.f]{1}), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Column1.v.v.f"}), #"Expanded key" = Table.ExpandRecordColumn(#"Removed Columns", "key", {"v"}, {"key"}), #"Expanded Value" = Table.ExpandRecordColumn(#"Expanded key", "Value", {"v"}, {"value"}) in #"Expanded Value"
johnt75
Super User
Super UserOnce you get down to the last level of list, which has the key as one record and the value as another, you can create 2 new columns, one to grab each record, then expand the records out.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wqo5RKotRsoqG0kAqDYkbo+Tr6Bfq5ugcEhrkGhSjVKsDl3AJ9fWNVHAP8g8NAIrH1sKlMIzwDwjy9HNx9Ql2DfZx9HNBMcU1NMg/wJGAAT6HZwSHHW5zD0HR6uYINJGATk+/YNegEE9UO10sHI2cXV1Ndc2NzC10TQzcDHQd3ZxddI0MHS2djF0MXA2NnCDmxtYqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Parsed JSON" = Table.TransformColumns(#"Changed Type",{},Json.Document),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1", {"v"}, {"Column1.v"}),
#"Expanded Column1.v" = Table.ExpandListColumn(#"Expanded Column1", "Column1.v"),
#"Expanded Column1.v1" = Table.ExpandRecordColumn(#"Expanded Column1.v", "Column1.v", {"v"}, {"Column1.v.v"}),
#"Expanded Column1.v.v" = Table.ExpandRecordColumn(#"Expanded Column1.v1", "Column1.v.v", {"f"}, {"Column1.v.v.f"}),
#"Added Custom" = Table.AddColumn(#"Expanded Column1.v.v", "key", each [Column1.v.v.f]{0}),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Value", each [Column1.v.v.f]{1}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Column1.v.v.f"}),
#"Expanded key" = Table.ExpandRecordColumn(#"Removed Columns", "key", {"v"}, {"key"}),
#"Expanded Value" = Table.ExpandRecordColumn(#"Expanded key", "Value", {"v"}, {"value"})
in
#"Expanded Value"
BIanonHelper V
Helper VThank you for your reply.
I actually ended up solving it by using the BQ function TO_JSON_STRING() when reading the col from db. This worked like a charm but I will test out your solution as well, if for nothing else then to learn how for another time.