Solved

Parsing JSON returned from Query List

  • 11 January 2017
  • 3 replies
  • 83 views

Badge +1

Hi,

 

What I want to do

  1. Query another list in the same site
  2. Get all the users in a single people or group field, get the email addresses of those users
  3. Save the email addresses in a string separated by a delimiter.
  4. Append another email address to the above string
  5. Save the string of email addresses to another people or group field in the same list (on which the workflow is running on)

 

What I am able to do

1. Query another list in the same site using Query list action

2. Get a list of email addresses in the people or group field. What I get is JSON

[{"Pool_x0020_Access_x0020_Users":[{"EMail":"Jake.Barton@email.com"},{"EMail":"Juan.AlonsoColmenares@email.com"},{"EMail":"B.Michels@email.com"},{"EMail":"Heather.Mellion@email.com"}]}]  

3. Get the first item from the above collection which returns a dictionary

{"Pool_x0020_Access_x0020_Users":[{"EMail":"Jake.Barton@email.com"},{"EMail":"Juan.AlonsoColmenares@email.com"},{"EMail":"B.Michels@email.com"},{"EMail":"Heather.Mellion@email.com"}]}

 

What I am not able to do

Get the email addresses in the dictionary into a string separated by a delimiter

 

I have a Query list action not Office 365 Query list action but a simple Query list action that gets data from the same site in a different list. The field I select is a people or group field which only returns people information. In my action the with property is set to work email and this is the JSON I get

 

[{"Pool_x0020_Access_x0020_Users":[{"EMail":"Jake.Barton@email.com"},{"EMail":"Juan.AlonsoColmenares@email.com"},{"EMail":"B.Michels@email.com"},{"EMail":"Heather.Mellion@email.com"}]}]        

 

From what I can tell

1. I have a collection

2. In the first index of the collection I have a dictionary with a key "Pool_x0020_Access_x0020_Users"

3. The value of the dictionary is another collection

 

I can use a get item from collection action to get the following JSON (after I log the output in a log to history action)

 

{"Pool_x0020_Access_x0020_Users":[{"EMail":"Jake.Barton@email.com"},{"EMail":"Juan.AlonsoColmenares@email.com"},{"EMail":"B.Michels@email.com"},{"EMail":"Heather.Mellion@email.com"}]}

 

Now I have a dictionary.

It would make sense to use get value from a dictionary action by passing the key "Pool_x0020_Access_x0020_Users" and storing the result in a collection and this is where my workflow fails.

 

How do I get a value from a dictionary which happens to be a collection? It is a collection, right?

 

What I want?

Is the actual email addresses. I want them in a string separated by a delimited so I can append another email address to this string and save the final result in another people or group field.

 

Thank you

icon

Best answer by TomaszPoszytek 11 February 2017, 00:28

View original

3 replies

Userlevel 6
Badge +15

So this might not be helpful, but I'm watching this thread (since I want to learn), and in an effort to try and bump the conversation, I found this post: 

http://orangevolt.blogspot.ca/2012/12/8-ways-to-query-json-structures.html#!/2012/12/8-ways-to-query-json-structures.html

Which might be of some help, maybe?

Userlevel 7
Badge +17

Hi!

I guess the response JSON code has a bit more values  It should begin with "d/results" etc...

So you could try to build a path d/results/Pool_x0020_Access_x0020_Users/EMail - if you could preview your JSON response and format it ex. using https://jsonformatter.curiousconcept.com/ then you will see how the path should be built.

On the other hand when you have the response as a string, you can simply use regular expression action to extract all e-mail addresses. Use the below regex:

(?<=(EMail"":""))[^"]+

This will return you a collection of e-mail addresses‌:

Then just use the "Join Items In Collection" action using semicolon as a delimiter and voilla! You et what you expect

Regards,

Tomasz

Badge +3

thanks, it works like a charm

Reply