Forum Discussion
Extracting 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":"MANUFACTURER"},{"v":"DUMMY GROUP"}]}},{"v":{"f":[{"v":"OPRINDELSESLAND"},{"v":"EUROPA"}]}},{"v":{"f":[{"v":"LØSVÆGT"},{"v":"FALSE"}]}},{"v":{"f":[{"v":"INSERTID"},{"v":"D8A2CEE5-7278-40F0-AFCD-21A9B3D0E12B"}]}}]}
These are key/value pairs and I am trying to extract it in two seperate columns.
I can parse the col as JSON and then expand record -> unpack list a bunch of times and I end up with a long list of both keys and values but I cannot figure out how to get it as seperate columns.
Here is my advanced editor:
let
Source = GoogleBigQuery.Database(),
database = Source{[Name=#"project"]}[Data],
PBI_Schema = database{[Name="anonName",Kind="Schema"]}[Data],
DimProduct_Table1 = PBI_Schema{[Name="anonName2",Kind="Table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(DimProduct_Table1,{"Id", "Metadata"}),
#"Parsed JSON" = Table.TransformColumns(#"Removed Other Columns",{{"Metadata", Json.Document}}),
#"Expanded Metadata" = Table.ExpandRecordColumn(#"Parsed JSON", "Metadata", {"v"}, {"v"}),
#"Expanded v" = Table.ExpandListColumn(#"Expanded Metadata", "v"),
#"Expanded v1" = Table.ExpandRecordColumn(#"Expanded v", "v", {"v"}, {"v.1"}),
#"Expanded v.1" = Table.ExpandRecordColumn(#"Expanded v1", "v.1", {"f"}, {"f"}),
#"Expanded f" = Table.ExpandListColumn(#"Expanded v.1", "f"),
#"Expanded f1" = Table.ExpandRecordColumn(#"Expanded f", "f", {"v"}, {"v"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded f1", each ([v] <> null))
in
#"Filtered Rows"
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"
2 Replies
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"Thank 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.
