Fetch value from JSON response

[Anonymous]

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!

[ZhangKun · Accepted solution]

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"

[Anonymous]

Thank you @ZhangKun. It worked! 

[bhanu_gautam]

@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
)

[Anonymous]

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

[bhanu_gautam]

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

[Anonymous]

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 ?