Json formatting

[Vinny_LaRocca]

I am using some API calls to grab form submissions from HubSpot. I am having some trouble sorting the Json file that comes back. I basically want to take the most recent submission only. The text looks like this:

{
“results”: [
{
“submittedAt”: 1666391734903,
“values”: [
{
“name”: “firstname”,
“value”: “Vinny”,
“objectTypeId”: “0-1”
},
{
“name”: “lastname”,
“value”: “LaRocca”,
“objectTypeId”: “0-1”
},
{
“name”: “email”,
“value”: “xxx”,
“objectTypeId”: “0-1”
},
{
“name”: “company”,
“value”: “xxx”,
“objectTypeId”: “0-1”
},
{
“name”: “industry”,
“value”: “xxx”,
“objectTypeId”: “0-1”
],
“pageUrl”: “Form”
},
{
“submittedAt”: 1666365052410,
“values”: [
{
“name”: “firstname”,
“value”: “xxx”,
“objectTypeId”: “0-1”
},
{
“name”: “lastname”,
“value”: “xxx”,
“objectTypeId”: “0-1”
},
{
“name”: “email”,
“value”: “xxx”,
“objectTypeId”: “0-1”
},
{
“name”: “company”,
“value”: “Top Golf”,
“objectTypeId”: “0-1”
},
{
“name”: “industry”,
“value”: “Sports entertainment”,
“objectTypeId”: “0-1”
},
],
“pageUrl”: “XXX”
}
]
}

[supermanPunch (Arpan)]

Hi @Vinny_LaRocca ,

It seems that the Json Data provided had a missing curly braces which was making the json as invalid.

@Vinny_LaRocca

{
“name”: “industry”,
“value”: “xxx”,
“objectTypeId”: “0-1”
],
“pageUrl”: “Form”
},
{

But on correction of the format, we can get the validated data in a Json Object by performing a Deserialization using the Deserialize Json Activity.

@Vinny_LaRocca

I basically want to take the most recent submission only.

For this we would need to understand, what is meant by the recent submission, Is it the first data present in the json ? Also, If you could provide the Expected Output that you require from this data, we could provide suggestions on retrieving the same.

An Initial Check :

A Direct conversion to Datatable, since the values is a Json Array :

Newtonsoft.Json.JsonConvert.DeserializeObject(Of System.Data.Datatable)(strJson("results")(0)("values").ToString)

Let us know if you were able to achieve your necessary requirements.

[ppr (Peter)]

@supermanPunch

what is meant by the recent submission,

In collaboration with @supermanPunch , we would interpret submittedAt value as a unix timestamp

So an ordering on this would look like:

for the ordering we can rely on the number value and do not need to convert the unix timestamp. Later we can do e.g. for extraction purpose

Quick shot for only the values:

[Vinny_LaRocca]

Thanks for the help. I know i need to deserialize. My trouble came in when trying to parse specific data. In the case of the above what I want to take out and put into an excel doc is firstname,Vinny then Lastname,LaRocca and so on. Trying to use myJObject.SelectToken(“”).tostring did not work to for finding anything.

If I wanted to put “Vinny” into a variable, how could I do that?

[ppr (Peter)]

let us know what is overall requirement / targeted goal. So we can avoid to run in a XY Problem.

Lets summarize what we achieved so far:

• processing the JSON
• Filtering to the last submitted data
• Extraction of the values data

[Vinny_LaRocca]

This is what I have right now. The next step I need to preform is to assign variables I can use in an excel doc. I created variables firstName, lastName, email, company and industry. In this case I want to assign firstName = Vinny, lastName = LaRocca and so on.

[ppr (Peter)]

@Vinny_LaRocca

The next step I need to preform is to assign variables I can use in an excel doc

as introduced by Arpan

@supermanPunch

A Direct conversion to Datatable,

is maybe the targeted result instead of manually extraction

For manually extraction how about extraction into a dictionary

within assign dictExtraction | Dictionary(of String, String) =

Creating a datatable could look like this:

So we do feel that not the extractioninto variables is the main goal instead of writing an extraction result to Excel

[ppr (Peter)]

@Vinny_LaRocca

Maybe this will clarify. The end goal is to produce something like this. How I get there doesn’t really matter. I am curious however which way would be easiest.

Perfect, so get guided by @supermanPunch for the next steps

[supermanPunch (Arpan)]

@Vinny_LaRocca

Maybe this will clarify. The end goal is to produce something like this. How I get there doesn’t really matter. I am curious however which way would be easiest.

@Vinny_LaRocca

I basically want to take the most recent submission only

@Vinny_LaRocca ,

We have understood the format, but we still do not have a confirmation on the recent submission value. Do we take it as mentioned by @ppr in his post above or is it just the first item from the values array that we would need to consider ?

Maybe a Total Data (Values) Conversion to Datatable sorted by recent submission value would be beneficial, Which we could then perform the filtration of the required data from it :

The methods used in the workflow are corresponding to the methods mentioned above in Peter’s Post.
Json_ConvertToDatatable.zip (4.1 KB)

For a First Row Requirement, we could modify the Datatable input to Write Range / modify the Datatable itself using an Assign in the below manner :

OutputDT.AsEnumerable.Take(1).CopyToDatatable

Let us know if you are able to follow the workflow or facing any issues.