topic Re: Fetch value from JSON response in Power Query

Fetch value from JSON response

Anonymous — Wed, 19 Mar 2025 12:24:14 GMT

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!

Re: Fetch value from JSON response

bhanu_gautam — Wed, 19 Mar 2025 13:03:57 GMT

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
)

Re: Fetch value from JSON response

Anonymous — Wed, 19 Mar 2025 13:29:49 GMT

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=objectAttributeValues

I 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"

Re: Fetch value from JSON response

bhanu_gautam — Wed, 19 Mar 2025 13:33:26 GMT

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"

Re: Fetch value from JSON response

Anonymous — Wed, 19 Mar 2025 13:40:14 GMT

Thanks again!

I am getting the same error.

Moreover, I am not sure what was the change you did in my code. Can you please highlight ?

Re: Fetch value from JSON response

ZhangKun — Wed, 19 Mar 2025 13:50:57 GMT

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"

Re: Fetch value from JSON response

Anonymous — Wed, 19 Mar 2025 14:32:46 GMT

Thank you ZhangKun. It worked!