Getting JSON keys in flow

[Anonymous]

Hi below is the JSON format I am getting from planner API I want User id's from this.

{
  "planId": "plan id",
  "bucketId": "bucket Id",
  "title": "test multiple user",
  ...
  ...
  ..
  ..
  "assignments": {
    "User ID 1": {
      ...
      ...
    },
    "User ID 2": {
      ...
      ...
    },
    "User ID 2": {
      ...
      ...
    }
  }
}

Here I want "User ID 1", "User ID 2", "User ID 3". I cant use for each because it's an object, not an array. please help with this.

[Sru]

Hi @Anonymous

use parse jason and apply for each to grab the values using Dynamic content.

formated  your json  and tried, able to see user id values in for each loop. Hope it helps.

{
  "planId": "plan id",
  "bucketId": "bucket Id",
  "title": "test multiple user",
  "assignments": [{
    "User ID 1": "a",
    "User ID 2": "b",
    "User ID 3" : "c"
      }]
 
}

[Anonymous]

Thanks for the reply, but for each loop will work only if its array. But my scenario there is no array.

It's a javascript objects with the key-value pair, and I want the list of keys.

[v-xida-msft]

Hi @Anonymous,

Do you want to use "User ID 1", "User ID 2" and "User ID 3" within "Apply to each" in your flow?

Could you please share a screenshot of your flow's configuration?

Further, do you want to get the "User ID 1" key (User ID 2, User ID 3) or its corresponding value?

Yes, you are right. The input parameter of "Apply to each" action is required to fill an array rather than an object. So within your flow, you should convert the Object data into an array firstly, then you could use your JSON data within your "Apply to each" action.

If you want to get the "User ID 1" (User ID 2, User ID 3) key within a "Apply to each" action, I afraid that there is no way to get the "User ID 1" (User ID 2, User ID 3) key in Microsoft Flow currently. I have no idea how to get the JSON key within a "Apply to each" action.

If you want to get the corresponding value of "User ID 1" ((User ID 2, User ID 3)) key, please take a try with the following workaround:

• Add a proper trigger, here I use Flow Button trigger.
• Add a Compose action, Inputs field set to the original JSON format data that you provided as below:

{
  "planId": "plan id",
  "bucketId": "bucket Id",
  "title": "test multiple user",
  "assignments": {
    "User ID 1": {
      "Name": "Kris Dai"
    },
    "User ID 2": {
      "Name": "Angelina"
    },
    "User ID 3": {
      "Name": "Teresa"
    }
  }
}

• Add a "Compose 2" action, Inputs set to following formula:

array(outputs('Compose')?['assignments'])

• Add a "Apply to each" action, input parameter set to output of "Compose 2" action.
• Within "Apply to each", add a "Compose 3" action, Input set to following formula:

item()?['User ID 1']

Note: If you want to get the corresponding value of User ID 2 and User ID 3, please type following formula within above Compose 3 action:

item()?['User ID 2']

item()?['User ID 3']

Image reference:

The flow works successfully as below:

Best regards,

Kris

[Anonymous]

Thanks for your help.

But I need the JSON keys ('User ID 1', 'User ID 3', 'User ID 3') not values so the logic won't work for my scenario.

And even if you put JSON object inside an array you will get same JSON Object while iterating, you won't get the JSON keys or values. so solution won't work.

My goal is to get the user ids, Planner API providing the "assignments" JSON object (Not an array).

This JSON object will have "User ID" as key and "User Details" as values.

Since a task can be assigned to anyone, so values inside "assignments" object are dynamic.

I cant hard code the values to get the user id because I will not be knowing to whom the task is assigned.

[testasdfasfsafa]

I have the same problem...  I have JSON returning a bunch of Key and Values and ALL I want to do is to get the value of specific key... why is this so complicated?  Flow is supposed to be for power users, not for developers or ODATA gurus...

[StevenWade]

having the SAME issue with the planner API. not sure whether i'm more annoyed at flow (for not allowing the ability to grab the key from key/value pairs) or at the Planner API team (who thought it would be a great idea to use the user id as an object key instead of the value).

either way, i don't have any ability to associate user-readable names with assigned planner tasks.

[StevenWade]

If anyone is curious, here's the assignments object returned within the task object:

SyntaxEditor Code Snippet

 "assignments": {
      "96586a3d-314e-4146-8def-1dfe72a0a733": {
        "@odata.type": "#microsoft.graph.plannerAssignment",
        "assignedDateTime": "2018-10-08T14:47:31.8329019Z",
        "orderHint": "",
        "assignedBy": {
          "user": {
            "displayName": null,
            "id": "96586a3d-314e-4146-8def-1dfe72a0a733"
          }
        }
      },
      "5b9c9288-4e23-43ba-be30-3eca86e43459": {
        "@odata.type": "#microsoft.graph.plannerAssignment",
        "assignedDateTime": "2018-10-08T14:47:36.7608382Z",
        "orderHint": "",
        "assignedBy": {
          "user": {
            "displayName": null,
            "id": "96586a3d-314e-4146-8def-1dfe72a0a733"
          }
        }
      }
    }
  }

[StevenWade]

Ok...i came up with one hack of a solution for this that involved creating an Azure Function App (using the underscore.js library) to extract the user id keys, and then calling that app with an http POST from my Flow. It seriously shouldn't have to be this hard.

[royg · Accepted solution]

Hi, this may be a little too late for the initiator of this thread, but hopefully it may help others.

I give all the credit for this solution to the writer of this blog post (John) who faced the same requirement and suggested using xml/xpath methods.

The real magic is getting the assignments JSON object and converting it to XML.

to do that use a compose action that concatenates a root level to the assignments json (using the assignments JSON without the root fails because attirbutes at the top level get converted creating mutilpe root items).

Compose:

xpath(xml(json(concat('{ "root": ', string(body('HTTPTaskAssignments')), ' }'))

), '/root/assignments/*')

*Note - remember to add the blank space before and after the "root": and before the appended curly braces or your json won't be parsed to xml. Also, stringify the assignments json, This can be broken into several compose actions for readability.

The next step is to select the result xml nodes into an array using Select action, use the previous step's Output and map into a new property (UserId for example) the xmlnode name.

Expression:

xpath(item(), 'name(/*)')

That's it, now you can iterate the select action result to perform actions such as getting the user name etc.

Hope this helps someone!

[Anonymous]

Hi @royg,

Thanks for your great  and rigorous solution ! : )

thanks to the following expression :

xpath(item(), 'name(/*)')

we get the name or key (I do not know how to call it) of the node.

If I want to get the value of this node, how I do ?

Sincerely yours ,

Adrien

[roy-gilboa]

Hi @Anonymous,

I'm glad this actually helped someone.

To answer your question, I used @StevenWade's payload sample and queried the assignedDateTime field by adding an additional row to the Select action with the following mapping - AssignedDate:

xpath(item(), 'string(/*/assignedDateTime)')

Please let me know if that helps.

Roy 

[Anonymous]

That's what I wanted !!

Thanks a lot for your quick reply :DDD @roy-gilboa 

[gotovamsee]

Thanks @roy-gilboa , this got me very close to what I wanted but not there yet.

Can you help on how to extract the value matching the 'label' & 'prediction'? In the below payload case I want to get "

 0.7955204971" which is matching the 'prediction' value "7"?

{
  "data": [
    {
      "predictionValues": [
        {
          "value": 0.0011699726,
          "label": "1"
        },
        {
          "value": 0.0013717867,
          "label": "10"
        },
        {
          "value": 0.000863159,
          "label": "11"
        },
        {
          "value": 0.0008208039,
          "label": "12"
        },
        {
          "value": 0.0001870127,
          "label": "13"
        },
        {
          "value": 0.0001543465,
          "label": "14"
        },
        {
          "value": 0.0001551285,
          "label": "15"
        },
        {
          "value": 0.0001528833,
          "label": "16"
        },
        {
          "value": 0.0001922927,
          "label": "17"
        },
        {
          "value": 0.0001648636,
          "label": "2"
        },
        {
          "value": 0.0002391713,
          "label": "3"
        },
        {
          "value": 0.00060699,
          "label": "4"
        },
        {
          "value": 0.0150151647,
          "label": "5"
        },
        {
          "value": 0.0873161414,
          "label": "6"
        },
        {
          "value": 0.7955204971,
          "label": "7"
        },
        {
          "value": 0.0695031859,
          "label": "8"
        },
        {
          "value": 0.0265666002,
          "label": "9"
        }
      ],
      "prediction": "7",
      "rowId": 0
    }
  ]
}

I could get to the list using below

xpath(xml(json(concat('{ "root": ', string(body('JoblevelPredictions3B')), ' }'))
), '/root/data/predictionValues/*')

xpath(xml(item()), 'string(//value)')
xpath(xml(item()), 'string(//label)')

[royg]

Hi @gotovamsee,

I'm glad I could help you out.

You could use "Filter Array" action on the array you get from the "Select" action.

Since you asked for the XPATH statement, I think your following action should be another compose action (say Compose_1):

xpath(xml(outputs('Compose')), 'string(/root/data/predictionValues[label=../prediction]/value)')

Note, that this will retrieve the 1st value that matches the criteria (in your case there is only 1 element that fits this filter, so I guess that suits your requirement).
If you want to filter all elements that meet a criteria for example, you want to get all the values of labels smaller than the prediction, then you would add a Compose action (Compose_1) followed by a Select action:

Compose_1: 
xpath(xml(outputs('Compose')), '/root/data/predictionValues[label<../prediction]/value') 
Select: Compose_1 output 
Value : xpath(item(), 'string(/value)')

Hope this gets you what you need.

Regards,

Roy

[eliotcole]

If anyone is coming to this later on, I have added an answer in this Stack which indicates how you can make this work for any object and also retrieve the values of the items and place it in a new array for processing.

That particular one deals with non-empty values, but that's just a step that can be removed.