How to add JObjects to a JArray when serializing a DataTable to JSON using custom formatting rules?

Question

When attempting to serialize a DataTable to JSON using a custom format, I have some trouble adding JObjects (representing a row) to my JArray (representing the table).

So what I hope to make the table look like is something like this:

[
    "Orderline" : {"Item": "Table", "Quantity": "5", "Amount": "50$"},
    "Orderline" : {"Item": "Chair", "Quantity": "20", "Amount": "30$"},
    "Orderline" : {"Item": "Couch", "Quantity": "2", "Amount": "500$"}
]

I have tried using the Add method to the JArray but I cannot seem to get it to work properly.

At the beginning I have set the JArray to new JArray, so that it is in memory and then I will go along and add the JObjects to it one at a time.

I can see that the add method takes two arguments (Item as JToken and content as object) which makes me a little confused because I do not see anyone else addressing both arguments in other code snippets I have seen online.

JArray is a instance of class Newtonsoft.Json.Linq.JArray.

Desired JObject output:

{
  "Orderlines": {
    "Item": "Table",
    "Quantity": "5",
    "Amount": "50"
  }
}

Draft code:

Dim JsonObejct as JObject
Dim MyArray as Jarray
Dim Table as datatable

Set MyArray = new JArray

for each row in table     

JsonObject = Jobject.FromObject(
    New With {
    Key.Orderlines = New With{
        key.Item = row("Item").Tostring,
        key.Quantity = row("Quantity").tostring,
        key.Amount = row("Amount").tostring
    }
    }
    )
Myarray.add(JsonObject)
Next row

I work in UiPath Studio and do not have the code in the same place its separated in multiple activities, so please don't get caught in wrongly defined details in the code, everything works until the add to jarray part.

I use Vb .net in UiPath Studio so I would appreciate a solution on how to add the JObects to my JArray in vb .net.

Answer 1

If you simply need to fix your compilation, the following compiles successfully and generates the required JSON structure:

Public Module DataTableJsonExtensions
    Public Function ToSpecificOrderlines(ByVal table As DataTable) As JArray
        Dim MyArray as JArray = New JArray
        For Each row As DataRow In table.Rows
            ' Possibly you need to use CultureInfo.InvariantCulture in your ToString calls
            Dim JsonObject As JObject = JObject.FromObject(
                New With {
                    Key.Orderlines = New With{
                        key.Item = row("Item").ToString, 
                        key.Quantity = row("Quantity").ToString,
                        key.Amount = row("Amount").ToString
                        }
                    }
                )
            MyArray.add(JsonObject)
        Next row
        Return MyArray
    End Function
End Module

And then do

Dim myArray as JArray = DataTableJsonExtensions.ToSpecificOrderlines(table)

Demo fiddle #1 here.

However, it seems preferable to generalize the code to serialize any set of DataTable columns by name, with an optional specified format:

Public Module DataTableJsonExtensions
    ' Serialize all the columns of the table to an Orderlines array
    Public Function ToOrderlines(ByVal table As DataTable) As JArray
        Return ToOrderlines(table, table.Columns.Cast(Of DataColumn).Select(Function(c) c.ColumnName).ToArray())
    End Function

    ' Serialize the selected columns of the table to an Orderlines array with the specified culture and formats (if any)
    Public Function ToOrderlines(ByVal table As DataTable, ByVal columns() As String, Optional ByVal culture As CultureInfo = Nothing, Optional ByVal formats As IDictionary(Of String, String) = Nothing) As JArray
        culture = If (IsNothing(culture), CultureInfo.InvariantCulture, culture)
        Dim array as JArray = New JArray
        For Each row As DataRow In table.Rows
            Dim obj As JObject = new JObject (
                new JProperty("Orderlines", 
                    New JObject(columns.Select(Function(c) New JProperty(c, String.Format(culture, If(Not IsNothing(formats) AndAlso formats.ContainsKey(c), formats(c), "{0}"), row(c)))))
                )
            )
            array.add(obj)
        Next row
        Return array
    End Function
End Module

Then, to get the JSON shown at the top of your question, with the $ appended to the "Amount" property's value, do:

Dim formats = New Dictionary(Of String, String) From {{"Amount", "{0}$"}}
Dim myArray as JArray = DataTableJsonExtensions.ToOrderlines(table, {"Item", "Quantity", "Amount"}, Nothing, formats)

If you don't need the formatting after all, you call it as follows:

Dim myArray as JArray = DataTableJsonExtensions.ToOrderlines(table, {"Item", "Quantity", "Amount"}) 

Notes:

• You can use DataTableJsonExtensions.ToOrderlines(table) to serialize all columns of the table to an Orderlines array.

• In general serialization should always be performed using the invariant culture so that JSON serialized in one locale (say, the United States) can be deserialized in another locale (say, Europe) which uses a different decimal separator or DateTime format. ToString(), however, formats its values using the current culture. Thus I provided the option for you to specify a formatting culture in case you really want to serialize in CultureInfo.CurrentCulture. Passing Nothing for the culture results in using the invariant culture.

• In the JSON shown in the top of your question you append a $ to the Amount column, so I provided an optional dictionary of formats in case you need to add that when serializing.

Demo fiddle #2 here.