Blue Prism JSON to Collection Transformation

[ConnorJenkins]

Hi,

Looking to transform a API response in JSON format to a collection but have encountered an issue with the JSON format and JSON to Collection action.

The first JSON field is an index that converts to new columns in a collection, each response could have 0 to 100 items so i am looking to dynamically iterate through these to extract the required data into a structured collection, but i cannot find a viable solution.

so far i have tested:
1. iterate through each column and extract using 'Collection Utility'.'Read Collection Field', this errors when attempting to access the subcollections using a dynamic field name
2. Regex Match, the only regex action available to myself matches only the first instance, not each occurrence.
3. Javascript to rename JSON Index to a static value to create rows instead of columns, unfortunately my Javascript knowledge is not sufficient at the moment to build this, but am working on this as my current solution  

JSON Example:
{
"0":[{DATA}],
"1":[{DATA}]
}
Collection Generated;


Any help would be greatly appreciated, happy to provide any further information if required 🙂

[PabloSarabia]

Hi @Connor Jenkins

To undestand better the problem. In your JSON example, when you say "DATA" you mean you have to really like this:

{
  "0": {
    "field1": "0_value1",
    "field2": "0_value2",
    "field3": "0_value3"
  },
  "1": {
    "field1": "1_value1",
    "field2": "1_value2",
    "field3": "1_value3"
  }
}

And you want to know (for example) which collection has a specific value in its "field1"?


Or directly, DATA means only one value per item?


See you in the Community, bye 🙂

[ConnorJenkins]

Hi @PabloSarabia

Thank you for your response, I have added below more information on the JSON format and the final collection i am trying to create from it

​Expanded JSON:

{
  "0": [
    {
      "address": "Address1",
      "postcode": "PostCode1",
      "additional": {
        "Field1": "Data1",
        "Field2": "Data2"
      }
    }
  ],
  "1": [
    {
      "address": "Address2",
      "postcode": "PostCode2",
      "additional": {
        "Field1": "Data1",
        "Field2": "Data2"
      }
    }
  ]
}​

Final Output collection, aware it will require manipulation and iteration to produce this with the dynamic JSON length;

Address	Postcode	Field1	Field2
Address1	PostCode1	Data1	Data2
Address2	PostCode2	Data1	Data2

[ConnorJenkins]

Hi, @PabloSarabia

Thank you so much for the VBO, the code below makes sense including how to add further fields. I will need to check the governance within my team before I can import and test the VBO but dont forsee any issues with it.

In the case that I am not permitted to use a custom VBO, is it possible to execute this code within ​a process action without importing the VBO?

Really appreciate your help with this :)​​

[PabloSarabia]

Hi @Connor Jenkins

Yes, you can use the piece of code with out importing this VBO. Include it in one of your owns VBO and it should work.

Just in case, I try to make all of this logic with only BP stages. But I think is impossible. The first problem that you face with is when you need to read all the columns, cause the action that you mentioned before, "Get Collection Field" allways returns you a text value. (It gives you an error cause has a string cast inside)

I think there might be a possibility to make a loop that deletes the columns of the collection or something similar, but it would be quite inefficient.


See you in the Community, bye 🙂

[AhmedBasheer_M]

Hi ,

The object you shared in this thread. does not have any action. 

Best Regards
Ahmed B.