Output json arrays

Question
1

I’m transforming CSV to Json and would like to have Json arrays in the output. But I’m not sure this can be done with the current version of Easy Data Transform. (I’m on 1.20.0 trial)

My question is: Can the desired output as described below be achieved somehow. I’ve looked but couldn’t find any way.

The csv has columns like:
“Phone1_Customer1”
“Phone2_Customer1”
“Phone1_Customer2”
“Phone2_Customer2”

The prefered output would be like this:
[{
“Customers”: [
{
PhoneNumbers: [“238798239854”, "3284723874]
},
{
PhoneNumbers: [“8247284872”]
}
]
}]

I tried to achieve this by renaming the columns like so:
“Phone1_Customer1” → “Customers.0.PhoneNumbers.0”
“Phone2_Customer1” → “Customers.0.PhoneNumbers.1”
“Phone1_Customer2” → “Customers.1.PhoneNumbers.0”
“Phone2_Customer2” → “Customers.1.PhoneNumbers.1”

This gives me:
“Customers” {
“0”: {
“PhoneNumbers”: {
“0”: “238798239854”,
“1”: “3284723874”
},
}
“1”: {
“PhoneNumbers”: {
“0”: “8247284872”
}
}
}

Also tried this:
I tried to achieve this by renaming the columns like so:
“Phone1_Customer1” → “Customers[0].PhoneNumbers[0]”
“Phone2_Customer1” → “Customers[0].PhoneNumbers[1]”
“Phone1_Customer2” → “Customers[1].PhoneNumbers[0]”
“Phone2_Customer2” → “Customers[1].PhoneNumbers[1]”

And even tried using the same column name twice in tho hopes it would magically turn into an array:
“Phone1_Customer1” → “Customers.PhoneNumbers”
“Phone2_Customer1” → “Customers.PhoneNumbers”
“Phone1_Customer2” → “Customers.PhoneNumbers”
“Phone2_Customer2” → “Customers.PhoneNumbers”

To no avail :slightly_frowning_face:

2

The closest I could get is to convert this:

Phone,Customer
Phone1,Customer1
Phone2,Customer1
Phone1,Customer2
Phone2,Customer2

To This:

[
  {
    "Customers": {
      "PhoneNumbers": "Phone1,Phone2"
    }
  },
  {
    "Customers": {
      "PhoneNumbers": "Phone1,Phone2"
    }
  }
]

We might have enough flexibility to do what you want one day, but we aren’t there yet.

3

Thank you for confirming.
You have an oustanding product that is a very useful tool already. Keep it up!

I do hope you can deliver on this feature one day…

1 Like
4

Possibly we could use some sort of special character in a column to denote that it is to be output as an array. We’ll have to give it some thought.

5

Intuitively I went looking for an output type in the json output, where you can configure the column types. I was looking for some checkbox for arrays there…
You’d also have to denote the type of the items in the array. (although technically a json array can contain different types)

Could try something like how HTML forms work. When multiple columns have the same name the values are all pushed into an array. Only thing then is that you cannot control the order of the values inside the array. (maybe on column order)

That way there is no need for special characters, no risk of breaking existing transforms.
Only a checkbox in the json output column type. Columns with the same name do get in the same array.

Take the phonenumber example:
Customer.Phone, 'Phone1"
Customer.Phone, “Phone2”
results in {“Customer”: {“Phone”:[“Phone1”, “Phone2”]}}

This doesn’t work with nested properties though, because there is no way to tell two seperate customers apart (like the first example in the original question)

Then there is the Customer.0.Phone.0 and Customer.0.Phone.1 approach where these numbers represent arrays and the position in the array. Don’t know if this would break anything for existing transforms. (Not if you could provide an array checkbox somewhere within the json output settings)
I think this is the most easy to understand approach for most people. (I left the previous HTML thing in here for inspiration maybe)

Just my 2 cents…

1 Like
6

Thanks for the feedback. We will certainly bear it in mind when we revisit this.

7

I am also interested in this issue. Its been 12 months since, have you revisted this option ?

8

It is still on the wishlist of new features to implement.