Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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-...
Need Help
Show and Tell
  • ZhangKun's avatar
    ZhangKun
    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"

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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"

 

 

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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's avatar
    Anonymous
    Not applicable
    1 year ago

    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 ?