Forum Discussion
Anonymous
1 year agoNot applicable
Fetch value from JSON response
Hi, I would like to fetch "red" value based on "objectTypeAttributeId": "1235". Can anyone help me with the expression to fetch ? JSON response: "attributes": [ { "workspaceId": "xxx-xxxx-...
- 1 year ago
A complete test code, please pay attention to the comments:
let data = Json.Document("{ ""attributes"": [ { ""workspaceId"": ""xxx-xxxx-xxxx-xxxx-xxxxxxxx"", ""globalId"": ""xxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxx"", ""id"": ""12345678"", ""objectTypeAttributeId"": ""1234"", ""objectAttributeValues"": [ { ""value"": ""ABCD-1264"", ""searchValue"": ""ABCD-1264"", ""referencedType"": false, ""displayValue"": ""ABCD-1264"" } ], ""objectId"": ""1264533"" }, { ""workspaceId"": ""xxx-xxxx-xxxx-xxxx-xxxxxxxx"", ""globalId"": ""xxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxx"", ""id"": ""12345687"", ""objectTypeAttributeId"": ""1235"", ""objectAttributeValues"": [ { ""value"": ""Red"", ""searchValue"": ""Red"", ""referencedType"": false, ""displayValue"": ""Red"" } ], ""objectId"": ""1264533"" } ] }"), data2 = Json.Document("{ ""attributes"": [ { ""workspaceId"": ""xxx-xxxx-xxxx-xxxx-xxxxxxxx"", ""globalId"": ""xxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxx"", ""id"": ""12345678"", ""objectTypeAttributeId"": ""1234"", ""objectAttributeValues"": [ { ""value"": ""ABCD-1264"", ""searchValue"": ""ABCD-1264"", ""referencedType"": false, ""displayValue"": ""ABCD-1264"" } ], ""objectId"": ""1264533"" } ] }"), #"Expanded Column" = #table({"Col"}, {{data}, {data2}}), #"Added Custom" = Table.AddColumn( #"Expanded Column", "Name", // Replace [Col] with the actual column name each List.Select([Col][attributes], each [objectTypeAttributeId] = "1235"){0}?[objectAttributeValues]?{0}?[searchValue]? ) in #"Added Custom"
ZhangKun
Super User
1 year agoA complete test code, please pay attention to the comments:
let
data = Json.Document("{
""attributes"": [
{
""workspaceId"": ""xxx-xxxx-xxxx-xxxx-xxxxxxxx"",
""globalId"": ""xxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxx"",
""id"": ""12345678"",
""objectTypeAttributeId"": ""1234"",
""objectAttributeValues"": [
{
""value"": ""ABCD-1264"",
""searchValue"": ""ABCD-1264"",
""referencedType"": false,
""displayValue"": ""ABCD-1264""
}
],
""objectId"": ""1264533""
},
{
""workspaceId"": ""xxx-xxxx-xxxx-xxxx-xxxxxxxx"",
""globalId"": ""xxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxx"",
""id"": ""12345687"",
""objectTypeAttributeId"": ""1235"",
""objectAttributeValues"": [
{
""value"": ""Red"",
""searchValue"": ""Red"",
""referencedType"": false,
""displayValue"": ""Red""
}
],
""objectId"": ""1264533""
}
]
}"),
data2 = Json.Document("{
""attributes"": [
{
""workspaceId"": ""xxx-xxxx-xxxx-xxxx-xxxxxxxx"",
""globalId"": ""xxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxx"",
""id"": ""12345678"",
""objectTypeAttributeId"": ""1234"",
""objectAttributeValues"": [
{
""value"": ""ABCD-1264"",
""searchValue"": ""ABCD-1264"",
""referencedType"": false,
""displayValue"": ""ABCD-1264""
}
],
""objectId"": ""1264533""
}
]
}"),
#"Expanded Column" = #table({"Col"}, {{data}, {data2}}),
#"Added Custom" = Table.AddColumn(
#"Expanded Column",
"Name",
// Replace [Col] with the actual column name
each List.Select([Col][attributes], each [objectTypeAttributeId] = "1235"){0}?[objectAttributeValues]?{0}?[searchValue]?
)
in
#"Added Custom"
- Anonymous1 year agoNot applicable
Thank you ZhangKun. It worked!