JsonArray to Datatable

Help Studio
1

Please it’s urgent guys,

Can anyone please help me for converting json array into datatable ? MY array format is different for each key value.
Thanks in advance.

Regards,
Hemal

2

@hemal Is it possible for you to send the json array ? Also have you tried using Deserialize Json Array Activity?

3

hi hemal

please try this workflow which will convert jarray to datatable
JsonArrayToDT.xaml (11.5 KB)

Thank you

4 Likes
4

Hi, deserialize the JSON, input the string and out we’ll have a json called out_Json.

Depending on how your array is looking you can access individual elements from it with this syntax out_Json.SelectToken(“items”).First(“title”).ToString, you’ll have to experiment around with the things between " " and see your results.

Then if you want to add those individual values in a datatable, if you already have the rows loop through it and use an assign with row(“ColumnName”) = valueFromJsonArray, or if you need to add rows, use a build data table to build your structure and then use an add data row. In the ArrayRow property you’ll need to use this syntax {valueFromJson1,valueFromJson2 etc}. As a reminder the structure of your array should follow the structure of your data table.

Bobby

5

Hi hemal,

You can also try this method
assign strJson= “[{‘name’: ‘John’,‘age’: 31,‘city’: ‘New York’},{‘name’: ‘praneeth’,‘age’: 32,‘city’: ‘chennai’}]”
assign myDt = JsonConvert.DeserializeObject(of datatable)(strJson)

Thank you

5 Likes
6

Hi @supermanPunch I already have tried with each and every scenario.
Let me send you:
{
‘data’: [{
‘date’: ‘09-OCT-2018’,
‘initial_date’: ‘29-APR-2018’,
‘country’: ‘India’,
‘report’: ‘Report’,
‘id’: ‘’,
}],
‘reporting’: [{
‘sal’: ‘Engineer’,
‘first_name’: ‘Hemal’,
‘middle_name’: ‘A’,
‘last_name’: ‘B’,
‘suffix’: ‘’,
‘institution’: ‘’,
‘department’: ‘’,
‘country’: ‘India’,
}],
‘patient’: [{
‘first_last_name_initials’: ‘UNKNOWN’,
‘patient_ID’: ‘’,
‘birthdate’: ‘02-MAY-1980’,
‘age’: ‘37’,
‘units’: ‘Years’,
‘sex’: ‘Male’,
‘product_indication’: [{
‘reported_indication’: [‘hypothyroidism.’, ‘thyroid (ARMOUR THYROID)’, ‘anxiety’],
‘coded_indication’: ’ ',
}],
}],
}

I am not sharing all the data but kind of this is the foramt as thare are too many data with many key , value and even it has child key value also

7

Thanks @praneeth.peyyeti Let me try :slight_smile:

8

@hemal Ok, Can you also show us thee Expected Datatable format you want? With what are the headers you need? If you show it in an Excel File it would be good.

Do this if Solution isn’t found yet :sweat_smile:

9

Actually datatable would be like all keys would be headers

10

@supermanPunch thanks in advance but i really don’t have much time for creating the excel. I am working on it. I will provide u in sometime til lthen can you please tell me how did you do

11

@hemal Do you need ‘date’, ‘initial_date’, ‘country’ as headers or as Values to it’s Keys?

12

sample.xlsx (9.0 KB)

pfa just some data(s) only i have written

1 Like
13

that would be headers

1 Like
14

@hemal ok. It looks a bit complicated to achieve it :sweat_smile:, but i’ll try

15

@hemal
have a look on this block

"data ": [{
		"date": "09 - OCT - 2018 ",
		"initial_date": "29 - APR - 2018",
		"country": "India",
		"report": "Report",
		"id": ""
	}],

Here we do have an array. If additional element would occur then it could be the case that it comes into another structure or violate the strcture. So it has risk to disturb a fixed horizontal column structure

In such a scenario a more generic column structure is to suggest. An example could look like this:

grafik
grafik426×700 17.7 KB

so unforseen structures can be later deeper parsed as we do store the JSON string e.g.product_indication

Also this structure can be transformed into the structure of your EXCEL if the processed structure is consistent for all records

Find demo XAMl here
hemal.xaml (17.1 KB)

As I was forced to correct your JSON (it was not valid by copy and paste) run the XAML against my JSON file from here
data.json (673 Bytes)

1 Like
16

Good Evening,
I have the same issue.
I have a Json Array.
I could read the Json, Do a “Deserialize JSON Array” and do a loop “For Each” to filter with a Key “TEST1_ID” and i can log the result. but i can’t write the result in a DataTable and in a Excel File.
i’ll put an example of the json file. The structure is the same.

[
{
“_idx” : “testlg”,
“_type” : “_test”,
“_id” : “test1”,
“_version” : 1,
“_source” : {
“TEST1_ID” : “f358Y34958Y34958Y3458Y34958Y3495Y83495”,
“TEST1_NAME” : “GENDER_U”,
}
},
{
“_index” : “testlg”,
“_type” : “_test”,
“_id” : “test1”,
“_version” : 1,
“_source” : {
“TEST1_ID” : “f358Y34958Y34958Y3458Y34958Y3495Y88787”,
“TEST1_NAME” : “GENDER_U”,
}
}
]

Thanks

17

How to get this into data table
[
{
“id”: 15,
“io-number”: “IND001000054”
},
{
“id”: 16,
“io-number”: “IND001000054”
},
{
“id”: 17,
“io-number”: “IND001000054”
},
{
“id”: 18,
“io-number”: “IND001000054”
},
{
“id”: 20,
“io-number”: “IND001000054”
},
{
“id”: 21,
“io-number”: “IND001000054”
},
{
“id”: 22,
“io-number”: “IND001000054”
},
{
“id”: 23,
“io-number”: “IND001000054”
},
{
“id”: 24,
“io-number”: “IND001000054”
},
{
“id”: 25,
“io-number”: “IND001000054”
},
{
“id”: 26,
“io-number”: “IND001000054”
},
{
“id”: 27,
“io-number”: “IND001000054”
},
{
“id”: 28,
“io-number”: “IND001000054”
},
{
“id”: 29,
“io-number”: “IND001000054”
},
{
“id”: 30,
“io-number”: “IND001000054”
},
{
“id”: 31,
“io-number”: “IND001000054”
},
{
“id”: 32,
“io-number”: “IND001000054”
},
{
“id”: 33,
“io-number”: “IND001000054”
},
{
“id”: 34,
“io-number”: “IND001000054”
},
{
“id”: 35,
“io-number”: “IND001000054”
},
{
“id”: 36,
“io-number”: “IND001000054”
},
{
“id”: 37,
“io-number”: “IND001000054”
},
{
“id”: 38,
“io-number”: “IND001000054”
},
{
“id”: 39,
“io-number”: “IND001000054”
},
{
“id”: 40,
“io-number”: “IND001000054”
},
{
“id”: 41,
“io-number”: “IND001000054”
},
{
“id”: 42,
“io-number”: “IND001000054”
},
{
“id”: 43,
“io-number”: “IND001000054”
},
{
“id”: 44,
“io-number”: “IND001000054”
},
{
“id”: 45,
“io-number”: “IND001000054”
},
{
“id”: 46,
“io-number”: “IND001000054”
},
{
“id”: 47,
“io-number”: “IND001000054”
},
{
“id”: 48,
“io-number”: “IND001000054”
},
{
“id”: 49,
“io-number”: “IND001000054”
},
{
“id”: 50,
“io-number”: “US032000050”
},
{
“id”: 51,
“io-number”: “IND001000054”
},
{
“id”: 52,
“io-number”: “IND001000054”
},
{
“id”: 53,
“io-number”: “US032000053”
},
{
“id”: 54,
“io-number”: “IND001000054”
}
]

18

Hi @Sakshi_Jain

There is a direct activitiy for converting json to datatable or

U can first deserialize the string to json and then add the Data as row to datatable by iterating through it

19

I getting cannot convert Json array to json object . Please share the xaml file

20

Hi Nived, what direct activity are you talking about? I do not want to build a new datatable and add all these columns to mimic the json using the build datatable activity… it’s a bit cumbersome