[resolved] API JSON output to Excel

Four Stars

[resolved] API JSON output to Excel

Hi All,
I have created a job (screenshot below) using REST Client & ExtractJSON Fields to insert data into excel or into Salesforce object. I need output data from extract json fields in multiple rows but I am getting data in single column with all the row values (Example below). Can you please provide suggestions to achive this.
Example:
Title                       Status
 

Accepted Solutions
Twelve Stars

Re: [resolved] API JSON output to Excel

ok, let start from the end 
The similar problem present with many API systems, and each have different solution (later), but finally, for extract proper information, You must use proper JSON Path:

where (String)globalMap.get("v_key") it is Your ID
     
as You can see - we make a loop for all possible ID, than use each value as query parameter on next steps
and final result - as expected
This is was The end of story ... How to fetch this list of ID? it always different 
Some systems like Google FireBase where I meet this problem first time have special query parameter "shallow" which return on list of Keys
I not familiar with Mavelink, so You need read how to request only ID from workspace
Or You need write custom routine to do this from file.
-----------

All Replies
Twelve Stars

Re: [resolved] API JSON output to Excel

I think You need attach some pictures of Your Job, and provide little more information about API
If Your API return to You element as array and You extract JSON by JSONPath - You must use (where xxx - number in array)
some time more easy trick - remove by replaceAll [] and than use tNormalize  
the benefits - no loops, do not need know size of array
  
-----------
Four Stars

Re: [resolved] API JSON output to Excel

Hi Vapukov,
I have uploaded the job image but not sure why its not getting displayed in the post. 
I have attached sample output JSON data file from API. I tried to use tnormalizwe in same path due which i was getting multile rows.
API_Ouput.txt.txt
I am not able to add the screenshots or files . Can you please help.
Twelve Stars

Re: [resolved] API JSON output to Excel

It very easy - edit You post unless You will see pictures Smiley Happy
In other cases - no way to help You
-----------
Four Stars

Re: [resolved] API JSON output to Excel

Hi Vapukov,
Not sure what the issue i am not able to upload image or file.
Please share you email. I can forward the attachmants.
API JSON Output:
{
"count": 1,
"workspaces": {
"10380637": {
"title": "Dylan's Internal Projects",
"status": {
"color": "green",
"message": "Active",
"key": 300
},
"account_features": {
"time_trackable": false,
"has_time_entry_role_picker": true
},
"permissions": {
"can_upload_files": true,
"can_private_message": true,
"can_join": false,
"is_participant": true,
"access_level": "admin",
"team_lead": true,
"user_is_client": false,
"can_change_price": true,
"can_change_story_billable": true
},
"id": "10380637"
}
"10380638": {
"title": "Tom's Internal Projects",
"status": {
"color": "Red",
"message": "Active",
"key": 300
},
"account_features": {
"time_trackable": false,
"has_time_entry_role_picker": true
},
"permissions": {
"can_upload_files": true,
"can_private_message": true,
"can_join": false,
"is_participant": true,
"access_level": "admin",
"team_lead": true,
"user_is_client": false,
"can_change_price": true,
"can_change_story_billable": true
},
"id": "10380637"
}
"10380639": {
"title": "Luisa's Internal Projects",
"status": {
"color": "Yellow",
"message": "Active",
"key": 300
},
"account_features": {
"time_trackable": false,
"has_time_entry_role_picker": true
},
"permissions": {
"can_upload_files": true,
"can_private_message": true,
"can_join": false,
"is_participant": true,
"access_level": "admin",
"team_lead": true,
"user_is_client": false,
"can_change_price": true,
"can_change_story_billable": true
},
"id": "10380637"
}
},
"results":
}

Current OutPut:
Title                                                                                                    Status                          Id
   

I need optuput in multile rows.
Title                             Status   Id
Dylan's Internal Projects   Green  10380637
Tom's Internal Project       Red     10380638
Luisa's Internal Project     Yellow  10380637
Twelve Stars

Re: [resolved] API JSON output to Excel

it more than enough, I will answer little later today
by the way - what source of data (which database? or provider)
-----------
Four Stars

Re: [resolved] API JSON output to Excel

 I have getting data from Mavenlink API. 
Thanks,
Vikas.
Twelve Stars

Re: [resolved] API JSON output to Excel

ok, let start from the end 
The similar problem present with many API systems, and each have different solution (later), but finally, for extract proper information, You must use proper JSON Path:

where (String)globalMap.get("v_key") it is Your ID
     
as You can see - we make a loop for all possible ID, than use each value as query parameter on next steps
and final result - as expected
This is was The end of story ... How to fetch this list of ID? it always different 
Some systems like Google FireBase where I meet this problem first time have special query parameter "shallow" which return on list of Keys
I not familiar with Mavelink, so You need read how to request only ID from workspace
Or You need write custom routine to do this from file.
-----------
Twelve Stars

Re: [resolved] API JSON output to Excel

as additional post:
all described above, because in JOSN not included proper id - no any chances to extract Key Name from Talend
all other columns You can extract more easy:
  
but because id - it is not correct, some other system include _id in JSON body with unique name and all work fine
as example - they own docs, they have id in JSON body, so may be it just Yours error when copy, if all id different, result will be correct

and my first post related to normalise, was about array constructions similar to:
"results":
}

and again if You proper copy JSON, You can extract id list from Results:
  it is from documentation web page, but not from Your example
-----------
Four Stars

Re: [resolved] API JSON output to Excel

Thank you so much. I am able to get the desired output in tabular format.
Warm Regards,
Vikas.