Extracting key/value pairs from json

[BIanon]

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"

[johnt75 · Accepted solution]

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"

[BIanon]

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.