Web Services - Json Path

[TomasKochan1]

Hi, I'd like to extract a collection of zip codes from a JSON response such as:

https://jsonplaceholder.typicode.com/users

[
  {
    "id": 1,
    "name": "Leanne Graham",
    "username": "Bret",
    "email": "Sincere@april.biz",
    "address": {
      "street": "Kulas Light",
      "suite": "Apt. 556",
      "city": "Gwenborough",
      "zipcode": "92998-3874",
      "geo": {
        "lat": "-37.3159",
        "lng": "81.1496"
      }
    },
    "phone": "1-770-736-8031 x56442",
    "website": "hildegard.org",
    "company": {
      "name": "Romaguera-Crona",
      "catchPhrase": "Multi-layered client-server neural-net",
      "bs": "harness real-time e-markets"
    }
  },
  {
    "id": 2,
    "name": "Ervin Howell",
    "username": "Antonette",
    "email": "Shanna@melissa.tv",
    "address": {
      "street": "Victor Plains",
      "suite": "Suite 879",
      "city": "Wisokyburgh",
      "zipcode": "90566-7771",
      "geo": {
        "lat": "-43.9509",
        "lng": "-34.4618"
      }
    },
    "phone": "010-692-6593 x09125",
    "website": "anastasia.net",
    "company": {
      "name": "Deckow-Crist",
      "catchPhrase": "Proactive didactic contingency",
      "bs": "synergize scalable supply-chains"
    }
  }
]

I can get single value but a collection fails at run time, though compiler didn't find syntax error.


I tried $[?(@.address.zipcode)] which also results in error.
Could someone please advise the correct query?

------------------------------
Tomas Kochan
------------------------------

[ewilson]

Hi @TomasKochan2,

When you say a collection failed at run time did you try making the data item type Collection and then setting the JSON Path to just $? That should parse the entire JSON into the output Collection.

$

.address.zipcode would fail to convert to a Collection because Blue Prism expects to see the element name to use for naming the column in the Collection. In this case the matching values are just the zip codes themselves.

You could try using just $

.address mapped to a Collection data item. That would give you just the address values and then you could refence the zips as a column in the Collection. 

Cheers,​

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

[TomasKochan1]

Thank you @ewilson,
$ works, but * or ?() operators don't seem to work or I miss something.
Recently I tried:
- with defined and undefined output collection
- to serve same data by my localhost, but ​assigned key to that anonymous array like follows:


> so then I would refer as $.tomas_test

.address but still receiving:

I shouldn't parse all data to nested collection using $ root selector, as real data might reach collection limit.


------------------------------
Tomas Kochan
------------------------------

[TomasKochan1]

Just tried examples from Docs on the same data set they provide:
- only 2nd and last work.
Most of the time, we receive large JSON array from endpoint. It'd be very helpful to successfully extract wanted values straight to a collection.

------------------------------
Tomas Kochan
------------------------------

[ewilson]

Hi @TomasKochan2,
​
I've spent some time looking at this further. I think the only way you're going to be able to whittle this down to just the zipcode values is to use custom code and parse the JSON yourself. Here's an example of some code I threw together that does just that.

JArray jsonText = JArray.Parse(Response_Content);

DataTable dtZipCodes = new DataTable();

DataColumn column = new DataColumn("zipcode");
column.DataType = System.Type.GetType("System.String");
dtZipCodes.Columns.Add(column);

IEnumerable<JToken> zips = jsonText.SelectTokens("
.address.zipcode");
foreach (JToken zipcode in zips)
{
	DataRow row = dtZipCodes.NewRow();
	row["zipcode"] = (string)zipcode;
	dtZipCodes.Rows.Add(row);
}

ZipCodes = dtZipCodes;​

And here are some screenshots of the Web API definition and the Global Code section where you need to add a couple DLL and namespace references:




Cheers,

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