Json to data-table

Help Activities
, , , ,
1

Hi there I am trying to convert JSON to Data-Table to write in a spreadsheet, attaching a sample file, I am keep getting error on passing the string to Deserialize Json Array activity but its throwing error, while trying with Deserialize it as JObj its able to parse it but unable to convert it to Data-Table to get the data in spreadsheet. This Json is complicated one

@ppr @Nithinkrishna @marian.platonov @PrankurJoshi
Can you please have a look.

2

The JSON is a JObject
grafik
and holds a JArray under the value Property

the structure of the JArray is not in a format that it is directly convertable into a datatable.

We would suggest to more specify the datatable target structure and based on this setting up a conversion from json to the datatable

3

@ppr can you please elaborate more, I wanna achieve to get the json data to DT

4

so show aus a sample of the expected datatable, based on this we can check conversion strategies. thanks

5

hi @indrajit.shah ,

Build A dataTable with Required Columns

Iterate the Output JArray with Help of For Each and modify The Type Arg In Properties as shown Below

image
image1136Ă—115 9.13 KB

then use AddDataRow Activity inside the For each to Add into the DataTable .

For EX: if you Want to add the Value of title in to the DT, then write {Item(“title”).tostring.trim} in the AddDataRow Activity

I Hope this will Help You …
Cheers!!

6

I am expecting the below fields as output

Book1.xlsx (13.0 KB)

@kmaddikatla – let me try this method.

@ppr & @kmaddikatla I have updated the sample json file.

7

It’s not JArray but json array inside json object.

8

can you please Share the Sample Input for the Better Clarification…

9

@kmaddikatla & @ppr I have added the sample json in the topic

10

can you please highlight and show what are the required Fields your are expecting from Sample Input JSON

11

I have already attached, please have a look.

12

Hi @ppr, @kmaddikatla , @Yoichi

do you have any suggestion? I want to pick all the data with headers and then can delete the columns that doesn’t needed or required.

13

Such a retrieval task has in general following building blocks:

  • clear what is retrieved from static and from dynamic (e.g. loop over array, JOBject series)

We assume that you are e.g. interested on the JObjects from JArray hold under “value”

Following Statement allows you loop over these Objects (e.g. for each activity | TypeArgument: JObject)

myJObject("value").Cast(Of JObject)

then again check what is retrieved from this JObject static e.g.
grafik

and what comes dynamic e.g.
grafik

Just have a look on our description as we also introduced to different retrieval methods (direct Acces, acces with index into the array, SelekTokens method

When combining all these building blocks you can setup the retrieval for one DataTable Datarow and and let it loop over all needed “values” JObjects

14

I am lost.

So do I have to deal with each element separately ? although the JSON structure will be same but data will get added. have to extracted each and every data from all the properties and its array.

15

You will deal with the structures.

Identify how many excel rows will be created by an item from the values Array

e.g Value.customFields.templateProperties will create X rows per customfield
each entry in customfield Array will again create rows

similar you have to check for other involved arrays

From this analysis you can derive the needed nested loop structures and you can start with the implementation of the datarow column value extraction and generation

16

expected it to be dynamic, can’t we handle the dynamic way? all though there must be some workaround isn’t it?

@ppr , can you help me on the current json, I am trying to extract all the fields and it’s data for now.

17

Hello @indrajit.shah

i hope my answer will help the issue,

i cant attach a screenshot because the policies of organization
you should do :

  1. build data table contains columns that you want to add in excel
    2.use Deserialize JSON Array Activity and put as input the name of variable that contains json
    and put the output in variable lets call it “JSOARR”.

  2. for each item in “JSOARR”
    and put inside for each, assign activity for fields you want to get from json and store inside it the filed from json object as string like below:
    ID= convert.ToString(item.SelectToken(“ID”))
    age=convert.ToString(item.SelectToken(“name”))

  3. then use add data row and put id and age to store on build data table that you already created in step 1

  4. use write range and put name of build data table variable to store on excel sheet

step 5 should be outside the scope of “for each”

18

Hi @takyysh,

I have tried desealizing with array but it won’t work as it’s a Jobj nested with jarray.

I have already tried the 1st step earlier and it didn’t work, have you tried with my sample json?

I am trying to get all the headers and data of the same.

19

Take a look on this Video:

But You need to define some Fixed Fields Not All the fields is a lot of information there and I don’t understrand how you will process this.
You need to adress Items in one line but select maximum 10 items not 100 items.

Thanks,
Cristian

20

Unfortunately I need to get all.

Here I am completely stuck.