In case any one else would like to have their Web API json returned as a collection.

[TracySchultz]

#code #json #webapi

I know I've run into situations where I am getting multiple records returned in my JSON response
Below is how I've set it up so the Result Content is returned as a collection.

Common Code

Code Options

Add the DLL

• Newtonsoft.Json.dll

Add the Namespace Imports

• Newtonsoft.Json
• Newtonsoft.Json.Linq

Shared Code

public class JSONResults {
	//Class variables
		private string input;

	//Constructor
		public JSONResults (string input){
			this.input = input;
		}

	//Property
		public DataTable Results { get
			{
				DataTable dt = new DataTable();
				dt = JsonConvert.DeserializeObject<DataTable>(input);
				return dt;
			}
		}
	}

API Action

In Response past the following into your custom code area.

JSONResults results = new JSONResults(Response_Content);
Results = results.Results;

------------------------------
Tracy Schultz
Weaver LLC
TX
------------------------------

[jhogelp]

Thanks for share, @Tracy Schultz​

------------------------------
Jhogel Ponne
Senior RPA
Ernst & Young
America/Panama
------------------------------

[ewilson]

Hi @Tracy Schultz,

This is good stuff. Normally, I just define a Collection output item and then use JSON Path as the method and set the value to $ (which is the root object or array). This usually works well except in situations where an array of objects is returned and some property on the first object is set to null but in a later object it's populated with an actual type-value like true (boolean). Then you end up with an exception of the JSON Path processing.


I'm thinking you could address that with your approach as you have more control. So you could actually set up serializer settings. Something like this:

public class JSONResults {
  // Class variables
  private string input;
  private JsonSerializerSettings settings = new JsonSerializerSettings														                
  {														                        
    NullValueHandling = NullValueHandling.Ignore,														                        
    MissingMemberHandling = MissingMemberHandling.Ignore														                    
  };

  // Constructor
  public JSONResults (string input) {
		this.input = input;
  }

  // Property
  public DataTable Results { get
  {
    DataTable dt = new DataTable();
    dt = JsonConvert.DeserializeObject<DataTable>(input, settings);
    return dt; }
  }
}
​

 
What do you think?

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

[devneetmohanty07]

Amazing solutions on display there @Tracy Schultz and @ewilson

Thanks for sharing with us 🙂
​​

------------------------------
----------------------------------

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

[TracySchultz]

Looks good, my only current issue is nested JSON isn't working with what I supplied.

I am and was aiming for a general collection returned as I'm frequently interacting with API's and getting large amounts of data back and while I can just work with the RC it's one less step to get it all back as a collection.

My ideal would be to pass in a collection that defines the JSON values, including nested items, and then passes back a correctly formatted collection, possibly even filtering out unwanted columns.

In the mean time this works.

------------------------------
Tracy Schultz
Weaver LLC
TX
------------------------------