Collection Management: BP writes only one row from collection

bnhairy

New Member
Hi everyone,

I am trying to run the following process:

1-Getting JSON text from a website
2-Converting JSON text to collection (of 2 rows)
3-Writing the collection to Excel

The first two steps work perfectly. The problem occurs during step 3: Blue Prism writes only the first row. As you can see in the attached picture ("Collection properties - current values"), BP creates one row for each row (I do not know why). As a result, only the first row (image: "first row") is written in the excel file (picture: "Output on Excel").

This is caused by the "extra" intermediary row that is created by BP. As a matter of fact, I have tried to create manually a random collection Coll1 (image: "collection manually created") and BP did correctly write the entire collection. As you may have noticed, the collection I have manually created shows only one row that contains both "real" rows (image: "Collection manually created - current values). And this allows BP to copy all the table, as the "real" rows are directly listed one under the other.

Any idea how one can structure the first collection the same way as in the second collection so that BP writes the entire collection?

I have also tried the "Append rows to collection", "Add to queue" functions, etc. but no luck. At the end, the output remains the same which is only one row that is considered/treated.

Below is the original JSON code:

[{"employee":"first.last@google.com","car":"Peugeot 208 GT line","color":"Ruby red"},
{"employee":"another.first.another.last@google.com","car":"Mini Cooper","color":"Midnight black"}]

The goal is to be able to write this code directly into an excel spreadsheet.

Thank you very much for your support. Your help would be highly appreciated.

Best regards
 

Attachments

  • Collection manually created - current values.PNG
    Collection manually created - current values.PNG
    14.4 KB · Views: 528
  • Collection manually created.PNG
    Collection manually created.PNG
    13.7 KB · Views: 517
  • Collection Properties - Current Values.PNG
    Collection Properties - Current Values.PNG
    13.9 KB · Views: 479
  • First row.PNG
    First row.PNG
    15.6 KB · Views: 456
  • Output on Excel.PNG
    Output on Excel.PNG
    4.5 KB · Views: 437
Last edited:

VJR

Well-Known Member
Hi bnhairy ,

I'm not fully sure of JSON's array syntax but is BP considering it as only 1 element of the array due to the position of the braces?

[
{"employee":"first.last@google.com", "car":"Peugeot 208 GT line", "color":"Ruby red"},
{"employee":"another.first.another.last@google.com", "car":"Mini Cooper", "color":"Midnight black"}
]

You may have already done it right but I'm just wanting you to have it another look.
Do check the below links
https://www.w3schools.com/js/js_json_arrays.asp


https://stackoverflow.com/questions...ce=google_rich_qa&utm_campaign=google_rich_qa

Also open the properties for "First Row.png" and click on the dotted button and see if the 2nd element has gone onto the new line.

Another thing that you mentioned is, this is due to an additional row generated by BP. Does deleting that extra row with the already existing action in BP resolve the issue?
 

bnhairy

New Member
Hi VJR,

Many thanks for your reply. I have tried your suggestions but with no success-

I have an array of two rows (each containing the data). But BP considers this as an array that has two rows (as if they were separated), and in each row, one should click to find the data. Normally, BP should consider this as an array with the two rows directly one under the other

In my array (image: "Expected array"), I wish to have only one table with the rows as you can see. But BP retrieves only the first row.

This is the structure in the properties:

Collection stage
In Fields tab: Collection named "JSON:Array" (obliged to create it)
Within JSON:Array, 3 fields are present: "employee", "car" and "color"

When running the process, the following is found in the current values:

JSON:Array (collection)
Row 1 of 1
the actual first row
Row 1 of 1
the actual second row


This is what I am expecting in order for BP to treat data properly:

JSON:Array (collection)
Row 1 of 2
the actual first row
the actual second row


Only one row should be visible with "Row 1 of 2", and when clicking on it I should be able to view both rows one under the other (instead of two separate rows with each containing only one "real row").

Let me know if this is not clear.

Many thanks for your support.

Kind regards.
 

Attachments

  • Expected Array..PNG
    Expected Array..PNG
    6 KB · Views: 197

VJR

Well-Known Member
Hi bnhairy,

What is the complete Text string that you are passing to the 'json' parameter here?
Can you paste it here so that I can try the output.
1524559506898.png
 

bnhairy

New Member
Hi VJR,

It's the same as the one I mentioned above, the data item contains the following as you can see in the images:

[{"employee":"first.last@google.com","car":"Peugeot 208 GT line","color":"Ruby red"},
{"employee":"another.first.another.last@google.com","car":"Mini Cooper","color":"Midnight black"}]

Putting in the text in one or more lines makes no difference.

Thanks for your support.

Kind regards,
 

Attachments

  • Json text.PNG
    Json text.PNG
    6.8 KB · Views: 179
  • json to collection - output.PNG
    json to collection - output.PNG
    28.7 KB · Views: 196
  • json to collection.PNG
    json to collection.PNG
    21 KB · Views: 180
  • Data Item containing JSON.PNG
    Data Item containing JSON.PNG
    18.5 KB · Views: 183

VJR

Well-Known Member
Hi bnhairy,

The reason I enquired for the entire string is because I did not see double quotes in the above text. But I now see that you have put it in a Data item and then passed it.
When I try to run the action I get the below error.
1524563316256.png
The original code of the JSON VBO is untouched on my machine so I know this is not the error from it.
I read that in order to use this I need to install a 3rd Party JSON.NET Dynamic Link Library (DLL) which I cannot do on my machine so I am unable to check its output further. Probably someone who has tried it may be able to assist on this.
 

bnhairy

New Member
FYI, in the object stage I tried to do some testing.

I tested first the other way round object "Collection to JSON" to see how BP converts a simple table of two rows into JSON code.

You can see the table in image "Collection to JSON" (as you can see the real rows are one under the other). When running "collection to JSON" process (image: "Collection to JSON process"), this is the JSON code returned by BP:

[{"employee":"a","car":"b","color":"c"},{"employee":"d","car":"e","color":"f"}]

When re-using this same code in the other business object "JSON to collection", the output can be found in the image below; BP creates these two "Row 1 of 1" which themselves contain the "real" data (instead of simply putting all real rows one under the other).

1524581545395.png

I am guessing then that somehwere in the business object code, there is a certain part, where it is requested from BP to create these "intermediaries rows". It would be best if this function could be removed.

Below you will find the code:

Code:
    Private Class JSON
        Public Const Array As String = "JSON:Array"
        Public Const Null As String = "JSON:Null"
    End Class

    Public Function ConvertToJSON(ByVal dt As DataTable) As String
        Dim o As Object = SerialiseGeneric(dt, True)
        Return JsonConvert.SerializeObject(o)
    End Function

    Public Function SerialiseGeneric(ByVal o As Object, ByVal removeArray As Boolean) As Object
        Dim dt As DataTable = TryCast(o, DataTable)
        If dt IsNot Nothing Then
            Return SerialiseDataTable(dt)
        End If

        Dim dr As DataRow = TryCast(o, DataRow)
        If dr IsNot Nothing Then
            Return SerialiseDataRow(dr, removeArray)
        End If

        Dim s As String = TryCast(o, String)
        If s IsNot Nothing AndAlso s = JSON.Null Then
            Return Nothing
        End If

        If o IsNot Nothing Then
            Return o
        End If

        Return Nothing
    End Function

    Public Function SerialiseDataTable(ByVal dt As DataTable) As Object
        If  IsSingleRow(dt) Then
            Return SerialiseGeneric(dt.Rows(0), False)
        Else
            Dim ja As New JArray()
            For Each r As DataRow In dt.Rows
                ja.Add(SerialiseGeneric(r, True))
            Next
            Return ja
        End If
    End Function

    Public Function IsSingleRow(ByVal dt As DataTable) As Boolean
        If dt.ExtendedProperties.Contains("SingleRow") Then
            Return CBool(dt.ExtendedProperties("SingleRow"))
        End If
        'Fallback for older versions of blueprism
        Return dt.Rows.Count = 1
    End Function

    Public Function SerialiseDataRow(ByVal dr As DataRow, ByVal removeArray As Boolean) As Object
        Dim jo As New JObject()
        For Each c As DataColumn In dr.Table.Columns
            Dim s As String = c.ColumnName
            If removeArray AndAlso s = JSON.Array Then
                Return SerialiseGeneric(dr(s), True)
            End If
            jo(s) = JToken.FromObject(SerialiseGeneric(dr(s), False))
        Next
        Return jo
    End Function

    Public Function ConvertToDataTable(ByVal json As String) As DataTable
        Dim o As Object = JsonConvert.DeserializeObject(json)
        Return DirectCast(DeserialiseGeneric(o, True), DataTable)
    End Function

    Private Function DeserialiseGeneric(ByVal o As Object, ByVal populate As Boolean) As Object
        Dim a As JArray = TryCast(o, JArray)
        If a IsNot Nothing Then
            Return DeserialiseArray(a, populate)
        End If

        Dim jo As JObject = TryCast(o, JObject)
        If jo IsNot Nothing Then
            Return DeserialiseObject(jo, populate)
        End If

        Dim jv As JValue = TryCast(o, JValue)
        If jv IsNot Nothing Then
            Return jv.Value
        End If

        Return JSON.Null
    End Function

    Private Function GetKey(ByVal kv As KeyValuePair(Of String, JToken)) As String
        If kv.Key IsNot Nothing Then
            Return kv.Key.ToString()
        End If
        Return ""
    End Function


    Private Function DeserialiseObject(ByVal o As JObject, ByVal populate As Boolean) As DataTable
        Dim dt As New DataTable

        For Each kv As KeyValuePair(Of String, JToken) In o
            Dim type As Type = GetTypeOf(DeserialiseGeneric(kv.Value, False))
            dt.Columns.Add(GetKey(kv), type)
        Next

        If populate Then
            Dim dr As DataRow = dt.NewRow()
            For Each kv As KeyValuePair(Of String, JToken) In o
                dr(getKey(kv)) = DeserialiseGeneric(kv.Value, True)
            Next
            dt.Rows.Add(dr)
        End If

        Return dt
    End Function

    Private Function DeserialiseArray(ByVal o As JArray, ByVal populate As Boolean) As DataTable
        Dim dt As New DataTable

        Dim first As Type = Nothing
        For Each e As Object In o
            If first Is Nothing Then
                first = GetTypeOf(DeserialiseGeneric(e, False))
            End If
            If GetTypeOf(DeserialiseGeneric(e, False)) IsNot first Then
                Throw New Exception("Data Type mismatch in array")
            End If
        Next
        If first IsNot Nothing Then
            dt.Columns.Add(JSON.Array, first)
        End If

        If populate Then
            For Each e As Object In o
                Dim dr As DataRow = dt.NewRow()
                dr(JSON.Array) = DeserialiseGeneric(e, True)
                dt.Rows.Add(dr)
            Next
        End If

        Return dt
    End Function

    Private Function GetTypeOf(ByVal o As Object) As Type
        If o Is Nothing Then Return GetType(String)
        Return o.GetType
    End Function

Many thanks.
 

Attachments

  • Collection to JSON.PNG
    Collection to JSON.PNG
    13.3 KB · Views: 185
  • Collection to JSON Process.PNG
    Collection to JSON Process.PNG
    17.1 KB · Views: 178

VJR

Well-Known Member
Hi bnhairy,

Yesterday while communicating with you I did check the code stage and thought that it could be doing that by design.
The Code stage of 'JSON to Collection' calls the ConvertToDataTable function after passing the Json string as a parameter and again further calls are made from that function as below. Someone who has experience in serialization/deserialization of objects will be able to interpret the below code and its function calls.

Public Function ConvertToDataTable(ByVal json As String) As DataTable
Dim o As Object = JavaScriptConvert.DeserializeObject(json)
Return DirectCast(DeserialiseGeneric(o, True), DataTable)
End Function

In the interim are you able to create a combined collection out of the separate rows using the available Collection objects or are you saying both the 1 Rows have the same first row?
 
Last edited:

bnhairy

New Member
Hi VJR,

Thanks for your reply. We have actually managed to do a quick and dirty fix that works. In the upcoming days, we'll be sharing a clean version of the updated code.

With regards to your question, they are separate rows. Each "Row 1 of 1" contains a different row.

Cheers

Kind regards.
 

BenB0B0

New Member
Hi VJR,

Thanks for your reply. We have actually managed to do a quick and dirty fix that works. In the upcoming days, we'll be sharing a clean version of the updated code.

With regards to your question, they are separate rows. Each "Row 1 of 1" contains a different row.

Cheers

Kind regards.

Hi bnhairy,

Can you share what you did to fix this? Running into a similar issue.

Thanks
 

bnhairy

New Member
Hi bnhairy,

Can you share what you did to fix this? Running into a similar issue.

Thanks

Hi Ben,

Yes sure, this is the code I am using in the "initialise" tab of the JSON VBO.

Let me know if it works for you:

Code:
    Private Class JSON
        Public Const Array As String = "JSON:Array"
        Public Const Null As String = "JSON:Null"
    End Class

    Public Function ConvertToJSON(ByVal dt As DataTable) As String
        Dim o As Object = SerialiseGeneric(dt, True)
        Return JsonConvert.SerializeObject(o)
    End Function

    Public Function SerialiseGeneric(ByVal o As Object, ByVal removeArray As Boolean) As Object
        Dim dt As DataTable = TryCast(o, DataTable)
        If dt IsNot Nothing Then
            Return SerialiseDataTable(dt)
        End If

        Dim dr As DataRow = TryCast(o, DataRow)
        If dr IsNot Nothing Then
            Return SerialiseDataRow(dr, removeArray)
        End If

        Dim s As String = TryCast(o, String)
        If s IsNot Nothing AndAlso s = JSON.Null Then
            Return Nothing
        End If

        If o IsNot Nothing Then
            Return o
        End If

        Return Nothing
    End Function

    Public Function SerialiseDataTable(ByVal dt As DataTable) As Object
        If  IsSingleRow(dt) Then
            Return SerialiseGeneric(dt.Rows(0), False)
        Else
            Dim ja As New JArray()
            For Each r As DataRow In dt.Rows
                ja.Add(SerialiseGeneric(r, True))
            Next
            Return ja
        End If
    End Function

    Public Function IsSingleRow(ByVal dt As DataTable) As Boolean
        If dt.ExtendedProperties.Contains("SingleRow") Then
            Return CBool(dt.ExtendedProperties("SingleRow"))
        End If
        'Fallback for older versions of blueprism
        Return dt.Rows.Count = 1
    End Function

    Public Function SerialiseDataRow(ByVal dr As DataRow, ByVal removeArray As Boolean) As Object
        Dim jo As New JObject()
        For Each c As DataColumn In dr.Table.Columns
            Dim s As String = c.ColumnName
            If removeArray AndAlso s = JSON.Array Then
                Return SerialiseGeneric(dr(s), True)
            End If
            jo(s) = JToken.FromObject(SerialiseGeneric(dr(s), False))
        Next
        Return jo
    End Function

    Public Function ConvertToDataTable(ByVal json As String) As DataTable
        Dim o As Object = JsonConvert.DeserializeObject(json)
        Return DirectCast(DeserialiseGeneric(o, True), DataTable)
    End Function

    Private Function DeserialiseGeneric(ByVal o As Object, ByVal populate As Boolean) As Object
        Dim a As JArray = TryCast(o, JArray)
        If a IsNot Nothing Then
            Return DeserialiseArray(a, populate)
        End If

        Dim jo As JObject = TryCast(o, JObject)
        If jo IsNot Nothing Then
            Return DeserialiseObject(jo, populate)
        End If

        Dim jv As JValue = TryCast(o, JValue)
        If jv IsNot Nothing Then
            Return jv.Value
        End If

        Return JSON.Null
    End Function

    Private Function GetKey(ByVal kv As KeyValuePair(Of String, JToken)) As String
        If kv.Key IsNot Nothing Then
            Return kv.Key.ToString()
        End If
        Return ""
    End Function


    Private Function DeserialiseObject(ByVal o As JObject, ByVal populate As Boolean) As DataTable
        Dim dt As New DataTable

        For Each kv As KeyValuePair(Of String, JToken) In o
            Dim typ As Type = GetTypeOf(DeserialiseGeneric(kv.Value, False))
            dt.Columns.Add(GetKey(kv), typ)
        Next

        If populate Then
            Dim dr As DataRow = dt.NewRow()
            For Each kv As KeyValuePair(Of String, JToken) In o
                dr(getKey(kv)) = DeserialiseGeneric(kv.Value, True)
            Next
            dt.Rows.Add(dr)
        End If
        return dt
    End Function

    Private Function DeserialiseArray(ByVal o As JArray, ByVal populate As Boolean) As DataTable
        Dim dt As New DataTable
        Dim first As Type = Nothing
        For Each e As Object In o
            If first Is Nothing Then
                first = GetTypeOf(DeserialiseGeneric(e, False))
            End If
            If GetTypeOf(DeserialiseGeneric(e, False)) IsNot first Then
                Throw New Exception("Data Type mismatch in array")
            End If
        Next
        If first IsNot Nothing Then
            dt.Columns.Add(JSON.Array, first)
        End If
        If populate Then
            Dim dr As DataRow = dt.NewRow()
            Dim elementsDt As New DataTable
            For Each e As Object In o
                ' Problem is that, if e is of type JObject, DeserializeGeneric > DeserializeObject is creating a new DataTable for each element e.
                ' To solve this, we need to merge DataTables.
                If TryCast(e, JObject) IsNot Nothing Then
                    Dim element As DataTable = DeserialiseGeneric(e, True)
                    elementsDt.Merge(element, False, MissingSchemaAction.Add)
                Else
                    Dim obj = DeserialiseGeneric(e, True)
                    elementsDt.Rows.Add(obj)
                End If
            Next
            dr(JSON.Array) = elementsDt
            dt.Rows.Add(dr)
        End If
        Return dt
    End Function
    
    Private Function GetTypeOf(ByVal o As Object) As Type
        If o Is Nothing Then Return GetType(String)
        Return o.GetType
    End Function
 

Zed

New Member
Hello guys,

Using the information provided in these thread, I was able to get each JSON object in the collection as row and append into a new collection.

These are the steps for anyone with the same challenge:

Please read first bnhairy's post at the beginning of these thread, he was quite descriptive. Also the implementation was done using Blue Prism Version 6.

Using the Webservices-REST Utility and GET method, it was observed the output collection consists of JSON:Array objects which is a collection of just one item. So there are collection items in a collection.

So if the output collection is named "OutputCollection" for instance then each collection item would be picked using [OutputCollection.JSON:Array]

Using a loop, each collection item was picked and appended to new collection called "BookedResults" using the Collection Manipulation utility with action "Append Rows to Collection". The new collection "BookedResults" should contain rows representing each JSON:Array collection item.

See the attached images.
 

Attachments

  • Capture.PNG
    6.7 KB · Views: 241
  • Capture2.PNG
    6.8 KB · Views: 226
  • Capture3.PNG
    10.7 KB · Views: 224
  • Capture4.PNG
    10.7 KB · Views: 200

kiss4344

Member
Thanks Guys, got the same problem and resolved with the help of this thread.

Step:1 - Change your JSON VBO Initialise code
Step:2 - Follow @Zeh Steps
 

chris_21

New Member
Hi guys,
I am facing the same problem in some cases. I m using the JSON- VBO , Json to collection (Skipping Jarray). I have a response from a webservice
Everything was working fine but all of a sudden i m getting JSON: Array as collections. There was a change in the webservice but I cannot understand any changes in the response. Any idea on what could be the problem?
 

chris_21

New Member
More help on what i m facing is that in some cases the same item is numerical while on the other hand is null (text). That s the reason I get JSON: Array (2)
 

ernest_mg

New Member
Hi, i was update JSON VBO's initial page as @Zeh steps and show massage for 'JSON to Collection' step: Internal : Could not execute code stage because exception thrown by code stage: Column 'JSNO:Array' does not belong to the table. Could you help me?
 

DhernRPA

New Member
Hello y'all, I am running into issues when I put the code in the initialize stage of the VBO. My process breaks when I add it. I am able to get the response, but not parse through the Arrays in the Collection. Is there another way y'all found to do this?
 

immadisp

New Member
Hello guys,

Using the information provided in these thread, I was able to get each JSON object in the collection as row and append into a new collection.

These are the steps for anyone with the same challenge:

Please read first bnhairy's post at the beginning of these thread, he was quite descriptive. Also the implementation was done using Blue Prism Version 6.

Using the Webservices-REST Utility and GET method, it was observed the output collection consists of JSON:Array objects which is a collection of just one item. So there are collection items in a collection.

So if the output collection is named "OutputCollection" for instance then each collection item would be picked using [OutputCollection.JSON:Array]

Using a loop, each collection item was picked and appended to new collection called "BookedResults" using the Collection Manipulation utility with action "Append Rows to Collection". The new collection "BookedResults" should contain rows representing each JSON:Array collection item.

See the attached images.
This worked for me
 
Top