How to retrive value from nested collection and store it in data item?

[NehaGote]

Hi,

I am trying to retrive the value from nested collection and store it in data item.
I am using calculation stage whose expression is [Json to collection.result.JSON:Array.change_request.start_date] and storing it in dataitem. But after running calculation stage  blue prism is giving out the following error
Internal : Failed to evaluate expression '[Json to collection.result.JSON:Array.change_request.start_date]' - Referenced field is not a collection, but sub-field notation was used



Blue Prism's dot notation only has the capability to refer to collection fields and column name has dot in it. Hence I have tried to remove dot from header using Remove dot from header action from utility collection manipulation but that didn't worked .Also tried rename collection field, rename field action but that also didnt work.

Can someone help me with this?

------------------------------
Neha Gote
------------------------------

[John__Carter]

Hi Neha - so you're saying that the sub-collection 'JSON:Array' has a field with the name 'change_request.start_date'? So the structure is like this:

• Json to collection
  • Result
    • JSON:Array
      • change_request.start_date

But due to the dot in the field name, BP thinks you mean this, where 'change_request' is a collection?

• Json to collection
  • Result
    • JSON:Array
      • change_request
        • start_date

Would it be possible to modify the JSON before converting it to a collection, ie remove the problematic dot?

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------

[NehaGote]

Hi John,
 
I had tried to fix the json earlier but that didnt work .I used Json to Collection( Skipping array) action of Symphony Ventures - JSON Manipulation (Custom C#) object which I found on digital exchange which removed one level of nesting then I copied Json to Collection output to other empty collection using calculation stage which gave me the normal collection. Then used below code to remove dot from header which fixed the issue. Thank you 

For Each Array As DataColumn In Input_Collection.Columns
Array.ColumnName=Microsoft.Visualbasic.Replace(Array.ColumnName,".","_")
Next
Output_Collection = Input_Collection

------------------------------
Neha Gote
------------------------------

[John__Carter · Accepted solution]

Maybe try editing the JSON text before converting to collection. Use the Replace function in a Calc stage to change the dot to something else. The pass the new JSON string into the conversion Action.

If it's possible to paste an example of the JSON here, it might help us see your problem.

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------

[NehaGote]

Hi John yup I tried replace function in calculation stage it replaced all the dots in Json file. Thank you:)

------------------------------
Neha Gote
------------------------------