Json to data-table

Also, one more thing how should extract from templateProperties under customFields

image

hi @indrajit.shah
yes that’s right and you can do that without for each,

using assign activity and use like below example inside assign activity
convert.ToString(jobj.SelectToken(“value[0].owner.uniqueName”))

for each will loop for each braces {} but if there is in some braces {} nested braces {} i think will make an issue

but it will only get the data from the 1st block of JSON when using

value[0]
then do I have to declare all the block like if I have 200 blocks then 200 time I have to declare?

convert.ToString(jrr.SelectToken(“value[0].customFields[0].templateProperties”))

as you see in json custom field have and each {} inside that block have value start from 0
if you want second template property in custom field you should put convert.ToString(jrr.SelectToken(“value[0].customFields[1].templateProperties”))

@takyysh
I appreciate your suggestion but when you state to use [0] that is the index of the json right so how can I get data from 3rd or 4th index? I have to mention the index for every time right? but that will not work, as the json data is dynamic so I can expect 1 data or 1000 data, I can’t keep assigning I have to loop it through to get all the data, whatever it has.

also, with this

convert.ToString(jrr.SelectToken(“value[0].customFields[1].templateProperties”))

I am not getting string but array of it
image

Correct me If my wrong? I am looking for more dynamic way to get then of static way, I hope I can explain myself.

Hi @indrajit.shah,

Take it one object notation at a time.

  • Your “value” token contains a Jarray essentially.
  • Within that Jarray, you have n number of items
  • Within the 1st item you have your required keys
  • Some keys like uniqueName can only be obtained by parsing into another jObject “owner” for example.
  • And the above pattern continues into your entire Jarray (n number of items)

Use your Excel sample file and clone that table such that you get only the headers with no rows. This datatable is your output datatable to which you will add rows with values.

In your For each item (jobject) in value (jarray) you will have to handle the 1st degree tokens and some can result in a jarray (“commodities”:) some can be jobject (“owner”:{})

Each of these needs to be handled induvidually and the values you get can later be set in your output datatable.

I also recommend you to go through this wonderful explanation of JSON format : Learn JSON in 10 Minutes - YouTube

I don’t get it, even If I take one object at a time then I am putting “Value[0]” right but I have to consider the n object as well, right then I have to make it dynamic then how can I make this “Value[n]” coz the json will always have value more than one.

Can you please suggest ne how can I extract only these below mention header data ?
title | owner.name | contractId | customFields.templateProperties

for the first 3 I can be able to extract and able to create DT for the last one I can’t able to find a solution yet?

I am processing the same way, like @ppr has suggestion on the topic but at one point I am stuck now that is getting data from templateProperties[] under customFields property, as this filed have no key value, but only value.
image

You can modify in your for each that if the key is templateProperties then do not use .value but get the fitem(“fieldid”).ToString, I have not tested this, but It should return
“MasterDataField,ERPIntegrationField,ZoneLeftField,DetailsViewField,EditViewField,CreateViewField”

if it does not, may be you will have to handle the fitem within another for loop to get into the templateProperties. So that in each loop you fetch index values of templateProperties.

See similar handling here : ( data.ParentCrumbs)
json - Convert JArray of String from JObject to String Array in c# - Stack Overflow

This should work fine if the Json structure for templateProperties will always be constant (the way it is in your sample json file).

hi @indrajit.shah

if you want to use assigning, yes you want mention index every time.

to get specific data from array use :

convert.ToString(jrr.SelectToken(“value[0].customFields[0].templateProperties[0]”)) ==== MsterDataField


convert.ToString(jrr.SelectToken(“value[0].customFields[0].templateProperties[1]”)) ==== ERPIntegrationField

yes if you loop that is a better way but i think you should also make some configuration,

to make it dynamically i dont think if there is any activity for json instead of deserialize json or deserialize json array

suggestion to use it from excel there is a way on excel to retrieve data from external resource, and you can get data as json and excel will convert it automatically to data table and store in excel, search for that subject

I am getting output for templateProperties like below

[
  "MasterDataField",
  "ERPIntegrationField",
  "ZoneLeftField",
  "DetailsViewField",
  "EditViewField",
  "CreateViewField"
]

Also, one more concern arises, i.e, If I have multiple records under the templateProperties tag my rest of the extracted record will print other data that no. of time of templateProperties like below

title | owner.name	| contractId | customFields.templateProperties
Test1 |	Puser5		| CW2222244 | MasterDataField
Test1 | Puser5 		| CW2222244 | ERPIntegrationField
Test1 | Puser5 		| CW2222244 | ZoneLeftField
Test1 | Puser5		| CW2222244 | DetailsViewField
Test1 | Puser5		| CW2222244 | EditViewField

@takyysh
I am not looking for any particular activity but to make my code with bits and pieces and make it workable.

I am trying the way Peter has suggested but made some changes as per your input as well to make it workable, but I am stuck with the templateProperties tag which is a sub property of customFields tag and it contains array[]

hi @indrajit.shah

you can specify the specific field on array like the below:

convert.ToString(jrr.SelectToken(“value[0].customFields[0].templateProperties[0]”))

where templateProperties[0] = first item on template property and 1 the second, 2 the third

but in your way you have to try

item(“customFields[0].templateProperties[0]”) , maybe this will help
or item(“customFields[0]”).templateProperties[0]

But that way it won’t be dynamic as again here your suggestion to use the index, and that where the problem lies, I don’t know weather the json will have 5 items or 1 or non.

Can we try like get the count and then loop it and add to row and the print it in finalDT

actually dont have an idea regarding dynamic json object

but regarding count you can try jobj.Count.tostring

the templateProperties can be handled as others what we do in JSON and is similiar as additional nested loop to the starter help from above

Thank you for your inputs and suggestion, is there any way we can access and get value from the templateProperties tag from the 1st foreach loop only, without using the 2nd loop? like I am doing it, I have put that in screenshot

For the same topic (JSON to Datatable) I build a video Today:

full code here:

Thanks,
Cristian

1 Like

Thank you @Cristian_Negulescu , it’s a huge help, also I had sent you a personal message too, can you please have a look.

1 Like

yo b - how did u do long vertical / scrolling screenshot?

you don’t need to take a screenshot just right click on the sequence and select save image.