JSON to collection: store all items as text format

[sumire]

Hello,

I want to store all JSON items as text format in a collection, even if the value is only a number.

The items to be retrieved are not constant. Please share if you have any ideas.

[Nandhakumar]

Hi @sumire 

Utility - JSON: JSON to Collection automatically identifies the data type of the columns of the collection from json. From there we can do the following for small volume of records,

1. Create a defined collection with text data type and assign the values from original collection by looping.

Is it possible for you to have defined collection? or collection fields are dynamic?

thanks,

Nandha

[sumire]

Hello @Nandhakumar ,

The collection fields are dynamic. So, I can not define the collection.

[Nandhakumar]

Hi @sumire 

May i ask you, you dont even have any clue that what will be the name of the columns in collection?

Because if the columns are dynamic then we can create an assingment of the value using blue prism without knowing the column name.

My suggestion to create a collection with dynamic columns is this but without knowing the fields of the collection we can't assign the values,

1. Get fields of the collection after Json to collection

2. Loop the fields and use append field (Text) to collection action to add a field into a Final collection

this way we can create the collection with fields which are coming via json. but without having the list of fields possible in a json response not possible to assign.

let me know your thoughts!

thanks,

Nandha

[sumire]

I wrote Python code to change all items of JSON to text format.
However, even with the JSON converted using this code, the data type of the items is automatically determined when using Utility - JSON 😞

import json
input_file = 'input.json'
output_file = 'output.json'

import os
import sys
os.chdir(os.path.dirname(os.path.abspath(sys.argv[0])))

def convert_values_to_str(data):
    if isinstance(data, dict):
        return {str(k): convert_values_to_str(v) for k, v in data.items()}
    elif isinstance(data, list):
        return [convert_values_to_str(item) for item in data]
    else:
        return str(data)

try:
    with open(input_file, 'r', encoding='utf-8') as f:
        original_data = json.load(f)

    text_data = convert_values_to_str(original_data)

    with open(output_file, 'w', encoding='utf-8') as f:
        f.write(json.dumps(text_data, ensure_ascii=False, indent=4))

    print(f"Finished: {output_file}")

except Exception as e:
    print(f"Error: {e}")

input("Finished. Press Enter")

[Nandhakumar]

Hi @sumire ,

i would suggest you to write the code for converting the columns datatype into text of a collection.

Yes, JSON to collection utility automatically detects the data type. So first convert json to collection then write a code to change all the columns data type.

thanks,

Nandha

[Nandhakumar]

Hi @sumire 

Please try this code for converting all the fields into text data type. vb code just add it in collection manipulation utility as new page.

' Create a new DataTable to hold the output
Dim outputTable As New DataTable()

' Add columns as Text
For Each column As DataColumn In InputCollection.Columns
outputTable.Columns.Add(column.ColumnName, GetType(String))
Next

' Loop through each row and convert values to text
For Each row As DataRow In InputCollection.Rows
Dim newRow As DataRow = outputTable.NewRow()
For Each column As DataColumn In InputCollection.Columns
If row.IsNull(column) Then
newRow(column.ColumnName) = ""
Else
newRow(column.ColumnName) = row(column.ColumnName).ToString()
End If
Next
outputTable.Rows.Add(newRow)
Next

' Set the output
OutputCollection = outputTable

before this you can convert the json to collection.

Note: I have tested the code and it is working fine. but make sure to the values you are converting whether they stay the same or values change.

thanks,

Nandha

[sumire]

I decided to modify "Utility - JSON" VBO.

I altered the global code of the VBO.
By modifying it this way, JSON records are stored in the collection as plain text instead of as an array of [JSON:Array].

Here is the code for the modified parts:

Private Function DeserialiseArrayWithoutJArray(ByVal jarr As JArray, ByVal populate As Boolean) As DataTable
    Dim dt As New DataTable

    ' Column definition (fixed as string type for all)
    If jarr.Count > 0 AndAlso TypeOf jarr(0) Is JObject Then
        Dim firstObj As JObject = DirectCast(jarr(0), JObject)
        For Each prop As JProperty In firstObj.Properties()
            If Not dt.Columns.Contains(prop.Name) Then
                dt.Columns.Add(prop.Name, GetType(String)) ' Force the data type to be fixed as String
            End If
        Next
    End If

    ' Loading line data
    If populate Then
        For Each token As JToken In jarr
            If TypeOf token Is JObject Then
                Dim row As DataRow = dt.NewRow()
                For Each prop As JProperty In DirectCast(token, JObject).Properties()
                    row(prop.Name) = prop.Value.ToString()
                Next
                dt.Rows.Add(row)
            End If
        Next
    End If

    Return dt
End Function

[sumire]

Hello @Nandhakumar ,

The solution to add actions to the 'Collection Manipulation' VBO is also great. I will try it myself.

[Nandhakumar]

Hi @sumire ,

Great to hear that you are able to resolve the issue 🙂

Thanks,

Nandha