Forum Discussion
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-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"
},
My query snippet:
#"Added Custom" = Table.AddColumn(#"Expanded Column", "Name", each [attributes]{1}[objectAttributeValues]{0}[value]),
Result : "Red" (but need this value based on objectTypeAttributeId)
1 is hard-coded. I believe this has to be replaced with <"objectTypeAttributeId": "1235"> condition. So that the value will be fetched properly irrespective of the placement in the response.
I am not sure how to do that.
Please let me know if you need more details.
Thank you!
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"
6 Replies
Anonymous , Try using
#"Added Custom" = Table.AddColumn(#"Expanded Column", "Name", each
let
attributes = [attributes],
filteredAttributes = List.Select(attributes, each _[objectTypeAttributeId] = "1235"),
firstMatch = if List.Count(filteredAttributes) > 0 then filteredAttributes else null,
value = if firstMatch <> null then firstMatch[objectAttributeValues][value] else null
in
value
)Thank you bhanu_gautam for your resonse.
I am getting below error while using your code.
Expression.Error: We cannot apply field access to the type List.
Details:
Value=[List]
Key=objectAttributeValuesI missed you mention in my previous post that this JSON response has lot attributes like I have mentioned. I just posted for one such instance.
Meaning that there is one [objectTypeAttributeId] = "1235" for the all attributes listed. Just that we not sure about its position within one attribute list.
Here is my full code - hope this will be more clearer
let
url = "https://api.atlassian.com/jsm/assets/workspace/bcc5e987-a7e5-8398-8cc7-ea05j54479e9/v1/object/aql?startAt=0&maxResults=15000&includeAttributes=true",
body = "{""qlQuery"": ""objectType = Servers""}",Source = Json.Document(Web.Contents(
url,[
Headers=[#"Authorization"="Basic <emailaddress:API token>",#"Content-Type"="application/json"],
Content=Text.ToBinary(body)
]
)
),
values = Source[values],
#"Converted to Table" = Table.FromList(values, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"objectKey", "attributes"}, {"objectKey", "attributes"}),
#"Added Custom" = Table.AddColumn(#"Expanded Column", "Server", each
let
attributes = [attributes],
filteredAttributes = List.Select(attributes, each _[objectTypeAttributeId] = "1235"),
firstMatch = if List.Count(filteredAttributes) > 0 then filteredAttributes else null,
value = if firstMatch <> null then firstMatch[objectAttributeValues][value] else null
in
value)
in
#"Added Custom"Anonymous , Use this
let
url = "https://api.atlassian.com/jsm/assets/workspace/bcc5e987-a7e5-8398-8cc7-ea05j54479e9/v1/object/aql?st...",
body = "{""qlQuery"": ""objectType = Servers""}",
Source = Json.Document(Web.Contents(
url, [
Headers = [#"Authorization" = "Basic <emailaddress:API token>", #"Content-Type" = "application/json"],
Content = Text.ToBinary(body)
]
)),
values = Source[values],
#"Converted to Table" = Table.FromList(values, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"objectKey", "attributes"}, {"objectKey", "attributes"}),
#"Added Custom" = Table.AddColumn(#"Expanded Column", "Server", each
let
attributes = [attributes],
filteredAttributes = List.Select(attributes, each _[objectTypeAttributeId] = "1235"),
firstMatch = if List.Count(filteredAttributes) > 0 then filteredAttributes else null,
value = if firstMatch <> null then firstMatch[objectAttributeValues][value] else null
in
value
)
in
#"Added Custom"
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"Thank you ZhangKun. It worked!
