Json to data-table

ppr · August 30, 2022, 12:22pm

Reduce for your learning and the complexity the extraction volume and work in iterations on it

Level 1: Loop over the value items and nested loop over the corresponding customfields fields

You see with this minimal implementation you got already a quick start
grafik

then continue and fetch the next informations in Level 2 iteration

takyysh · August 30, 2022, 12:37pm

first of all you should know that there is no activity to deserialize any json structure and store it in excel or any data table, but you should deserialize then configure your workflow to get specific fields of json you need.

anyway, you should use deserialize JSON activity instead of deserializeJSON Array activity.

you should know something regarding json:
each block in json have postion start from 0
each field in json have nested fields we should put
name of header.name of sub name to get it

for elaboration to help you get any filed you want from your json see below

use deserialize json activity and the result store in variable called: “jobj”
examples:

convert.ToString(jobj.SelectToken(“value[0].title”)) ==== Test 1

convert.ToString(jobj.SelectToken(“value[0].owner.uniqueName”)) === puser5

convert.ToString(jobj.SelectToken(“value[0].affectedParties”)) === ABC Ltd

convert.ToString(jobj.SelectToken(“value[3].affectedParties”)) === S Enterprise SDN BHD- demo

i hope that will help you i already tried it

indrajit.shah · August 30, 2022, 12:55pm

do you have any workaround? or any screenshot to share?

takyysh · August 30, 2022, 1:20pm

owner value is: convert.ToString(jrr.SelectToken(“value[0].owner.uniqueName”))

indrajit.shah · August 31, 2022, 12:09pm

Thank you for the reply, help me understand then how should I get the all date in a DT?

If I proceed as you suggested -
1st I will create all the required header under BuildDT
then read the JSON with Read text file
Then pass the output of above as input for Deserilalize Json
Then pass the output of above to For Each
with in loop assign all the headers items and then store it in Add data row
then pass the output to write range outside the loop to write the data

right? did I miss anything?

indrajit.shah · August 31, 2022, 1:16pm

Hi @ppr
I am unable to replicate your code, do I have to import any namespace?

m
Do you mind creating a small workflow on the sample data, if that’s okay with you, I will make the alterations accordingly.

ppr · August 31, 2022, 1:17pm

set the typeargument of the for each activity settings to JObject

indrajit.shah · August 31, 2022, 3:09pm

Got it.

btw what does this function states?

Value(Of String)

indrajit.shah · September 1, 2022, 8:21am

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

takyysh · September 1, 2022, 11:18am

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

indrajit.shah · September 1, 2022, 11:24am

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?

takyysh · September 1, 2022, 11:44am

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”))

indrajit.shah · September 1, 2022, 11:56am

@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

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

jeevith · September 1, 2022, 12:09pm

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

indrajit.shah · September 1, 2022, 12:15pm

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.

jeevith · September 1, 2022, 12:25pm

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).

takyysh · September 1, 2022, 12:58pm

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

indrajit.shah · September 1, 2022, 1:01pm

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

indrajit.shah · September 1, 2022, 1:06pm

@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[]

takyysh · September 1, 2022, 1:51pm

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]

Most Active Users - Yesterday
Anil_G
supermanPunch
vishal.kp
mohamedalthaf
Neal369
Deep_HML
Manish540
keerthana.h
Floris_Maat
AAydi
More details...