How to search a JSON response after converting collection

[StephanieStrydom]

Hi, I get a JSON response via API - a list of names with their details. I convert this response to a collection, which are collections in the collection. Any ideas on how I can search for a name without looping through the whole collection?

------------------------------
Stephanie Strydom
------------------------------

[SimonCooke1]

Hi Stephanie,

If you're just looking to see whether the name exists you could try an InStr function on the API response.

If you need the record attached to the name and you don't want to loop through a large collections of collections, have you checked the API documentation to see whether you can add query parameters to specify which record you want returning? 

If you have no other choice but to loop and are concerned about the time it takes BP to do that (as I have found in the past), then I'd be looking at a code stage to do the looping. In my experience it is a lot faster than the BP objects.

Simon

------------------------------
Simon Cooke
------------------------------

[chrispotvin]

Hi Stephanie, 

If understand the question correctly, is the name you are searching for in one of the nested collections? If so the only way is to loop the collections by calling the nested collections array. Please see attached below. 

Chris

------------------------------
Christopher Potvin
Senior RPA Developer
SS&C Blue Prism
Canada
------------------------------

[StephanieStrydom]

Hi Christopher, I managed to solve my problem. I downloaded the latest Utilities - JSON (10.0.1) from DX and there is an action to query the JSON using the JSONPath, and it returns the result in a collection, which is ideal.

------------------------------
Stephanie Strydom
------------------------------

[harish.mogulluri]

Other VBO you can utilize to acheive any json related  manipulation from DX exchabge -  (JSON Manipulation(Custom C#))

Also if  you have nested  collection  you can dump  nested collection in to new collection and  get the values directly from that collection.

------------------------------
-----------------------
If I answered your query. Please mark it as the "Best Answer"

Harish Mogulluri
Lead developer
America/New_York TX
------------------------------

[chrispotvin]

Awesome. Happy you were able to solve it. :) Can you post of screenshot of how you were able to query the JSON object? (if you can)

------------------------------
Christopher Potvin
Senior RPA Developer
SS&C Blue Prism
Canada
------------------------------

[StephanieStrydom]

Hi Christopher, the release came with process examples. My JSONPath was this: $.consultant[?(@.name == 'Alex Parker')]

with examples, my

------------------------------
Stephanie Strydom
------------------------------

[salman_shaik]

Hi @Stephanie Strydom,

As per your previous post i tried to search for a keyword within the JSON using the query but its not working for me can you pl look into it and correct me if any syntax is wrong?

I got a JSON response via API - list of words from the image. My requirement is i want to check whether the required keyword is present in the json or not. it should return me a true or false. I don't want to convert the json into a collection and looping it through whole collection and search for specific keyword.

To achieve this i have passed JSON Query using the Utility Json vbo provided by blueprism but it's throwing an error.

JSON Input:

{
"status":"succeeded",
"createdDateTime":"2023-08-14T14:11:24Z",
"lastUpdatedDateTime":"2023-08-14T14:11:25Z",
"analyzeResult":
{
"version":"3.2.0",
"modelVersion":"2022-04-30",
"readResults":
[
{
"page":1,
"angle":-0.1411,
"width":828,
"height":1472,
"unit":"pixel",
"language":"en",
"lines":[{"boundingBox":[112,262,728,263,727,344,112,341],
"text":"MATCHDAY",
"appearance":{"style":{"name":"other","confidence":0.972}},"words":[{"boundingBox":[113,263,700,263,696,345,117,340],"text":"MATCHDAY","confidence":0.995}]},{"boundingBox":[464,383,544,380,546,409,464,415],"text":"PARIS","appearance":{"style":{"name":"other","confidence":0.972}},"words":[{"boundingBox":[466,388,541,383,541,410,466,415],"text":"PARIS","confidence":0.955}]},{"boundingBox":[395,422,433,422,433,439,396,439],"text":"VS","appearance":{"style":{"name":"other","confidence":0.972}},"words":[{"boundingBox":[397,422,423,422,423,439,397,439],"text":"VS","confidence":0.997}]},{"boundingBox":[294,463,363,462,363,481,294,482],"text":"STADE","appearance":{"style":{"name":"other","confidence":0.972}},"words":[{"boundingBox":[296,463,357,463,357,482,295,483],"text":"STADE","confidence":0.994}]},{"boundingBox":[293,480,362,481,362,501,293,501],"text":"REIMS","appearance":{"style":{"name":"other","confidence":0.972}},"words":[{"boundingBox":[294,481,359,481,359,502,294,501],"text":"REIMS","confidence":0.995}]},{"boundingBox":[467,463,542,463,542,485,467,485],"text":"SAINT - GERMAIN","appearance":{"style":{"name":"other","confidence":0.945}},"words":[{"boundingBox":[470,463,493,472,491,484,467,473],"text":"SAINT","confidence":0.995},{"boundingBox":[495,473,499,473,497,485,493,484],"text":"-","confidence":0.31},{"boundingBox":[501,473,542,464,542,475,499,485],"text":"GERMAIN","confidence":0.663}]},{"boundingBox":[310,525,513,524,513,546,310,546],"text":"STADE AUGUSTE","appearance":{"style":{"name":"other","confidence":0.972}},"words":[{"boundingBox":[312,525,384,526,384,547,312,547],"text":"STADE","confidence":0.998},{"boundingBox":[397,526,507,525,507,547,397,547],"text":"AUGUSTE","confidence":0.997}]},{"boundingBox":[355,554,469,554,469,574,355,575],"text":"DELAUNE","appearance":{"style":{"name":"other","confidence":0.972}},"words":[{"boundingBox":[356,556,463,554,463,575,356,575],"text":"DELAUNE","confidence":0.993}]},{"boundingBox":[340,586,483,584,483,604,340,605],"text":"20:45 CEST","appearance":{"style":{"name":"other","confidence":0.972}},"words":[{"boundingBox":[342,587,404,586,404,605,342,605],"text":"20:45","confidence":0.994},{"boundingBox":[417,586,477,585,477,604,417,605],"text":"CEST","confidence":0.99}]},{"boundingBox":[780,960,811,954,813,975,780,980],"text":"PAR","appearance":{"style":{"name":"other","confidence":0.972}},"words":[{"boundingBox":[780,959,811,954,814,974,781,980],"text":"PAR","confidence":0.31}]},{"boundingBox":[273,1014,318,1008,320,1023,276,1031],"text":"PAR","appearance":{"style":{"name":"other","confidence":0.972}},"words":[{"boundingBox":[275,1016,308,1009,308,1025,277,1031],"text":"PAR","confidence":0.998}]},{"boundingBox":[277,1060,297,1074,292,1080,273,1067],"text":"BAINS","appearance":{"style":{"name":"other","confidence":0.945}},"words":[{"boundingBox":[277,1061,296,1073,292,1080,273,1067],"text":"BAINS","confidence":0.643}]},{"boundingBox":[14,1296,211,1299,210,1338,14,1335],"text":"OR LIVE L","appearance":{"style":{"name":"other","confidence":0.972}},"words":[{"boundingBox":[22,1297,70,1299,67,1337,19,1335],"text":"OR","confidence":0.998},{"boundingBox":[83,1299,170,1301,169,1338,80,1337],"text":"LIVE","confidence":0.989},{"boundingBox":[180,1301,202,1301,201,1337,179,1337],"text":"L","confidence":0.934}]},{"boundingBox":[514,1257,807,1230,810,1267,518,1296],"text":"ACCOR LIVE LIMIT","appearance":{"style":{"name":"other","confidence":0.972}},"words":[{"boundingBox":[515,1258,637,1247,642,1284,519,1296],"text":"ACCOR","confidence":0.995},{"boundingBox":[650,1246,721,1239,727,1276,655,1283],"text":"LIVE","confidence":0.988},{"boundingBox":[729,1238,806,1230,811,1267,734,1275],"text":"LIMIT","confidence":0.944}]},{"boundingBox":[290,1314,337,1316,336,1344,290,1340],"text":"ES","appearance":{"style":{"name":"other","confidence":0.972}},"words":[{"boundingBox":[291,1314,332,1316,330,1344,290,1341],"text":"ES","confidence":0.955}]},{"boundingBox":[327,1331,508,1331,508,1374,327,1374],"text":"UNIBET","appearance":{"style":{"name":"other","confidence":0.972}},"words":[{"boundingBox":[328,1332,504,1332,504,1374,327,1375],"text":"UNIBET","confidence":0.995}]},{"boundingBox":[360,1379,474,1378,475,1399,360,1400],"text":"......","appearance":{"style":{"name":"other","confidence":0.972}},"words":[{"boundingBox":[361,1379,468,1378,468,1400,361,1401],"text":"......","confidence":0.887}]}]}]}}

JSON Query:

$.analyzeResult.readResults.lines[?(@.text =='MATCHDAY')]

By using the above query i am trying to check whether the text property in Json input contains MATCHDAY or not?

Can you correct me if my query is wrong.

------------------------------
Salman Shaik
------------------------------

[StephanieStrydom]

Hi Salman, this was the first time I used jsonpaths and my needs matched the the example provided. I think the reason why the jsonpath query worked for me, is because I am matching to a single request in the collection, and then return the related data e.g. I have a list of bankers, and I need the find the right one and return their contact details. As I understand you want to filter the your list, is that correct? My colleague and I tried to filter a JSON response, but failed - we are now looping through the responses and finding the matching items. We parked it for later investigation. 

------------------------------
Stephanie Strydom
------------------------------

[Athiban_Mahamathi]

Hi Salman,

You can use InStr string function to look for your target keyword available in the JSON string. 

------------------------------
Athiban Mahamathi - https://www.linkedin.com/in/athiban-mahamathi-544a008b/
Technical Consultant,
SimplifyNext,
Singapore
------------------------------