Format JSON to Post to API

Format JSON to Post to API

  

Hi All, 

I have an aggregate which has two tables Appointment and People. What I am trying to to is post to an API which expects a specific JSON format as follows

{
"TableName": "TestTable",
"Item": {
"topic": "Test Topic",
"scheduleddate": "201703180422",
"owner": "testuser",
"completeddate": "201303190422",
"completedby": "testuser",
"department": "Department1",
"status": "Completed",
"people": ["testuser", "testuser2"]
}
}



so 

"topic": "Test Topic",
"scheduleddate": "201703180422",
"owner": "testuser",
"completeddate": "201303190422",
"completedby": "testuser",
"department": "Department1",
"status": "Completed" 


is in one table and 

"people": ["testuser", "testuser2"] in another however does anyone know how I can format the output so that it matches the above?


Regards


Hello Lee.

You have a relationship of one-to-many between your two tables.
This means that in your aggregate, for each "people", you will have the other information "repeated".

There is no way to "format" the output of the aggregate result list to match the info you need in the JSON. Not "directly".

You will have to create a structure that matches the JSON, than create a local list of that structure, iterate throgh your aggregate list result and fill the local list, guaranteeing that you will not duplicate the info on the first part, but only adding when it didn't exist yet, and add the info of people while the first part is "repeated".

But maybe you can return the peoples field as a string from an Advanced Query...

EDIT: Yes, you can use a trick like this to have the persons as a single column. Yet, while you will be able to convert directly the list result in an almost viable JSON, you will have to manipulate the JSON to transform the "person" in a list, as it will be a single string field in the output of the export of the list to JSON.

Cheers,
Eduardo Jauch 

 

Yes, the typical way to handle these 1:n relationships is to post-process the query output, keep track of a change in (in this case) TableName, and ListAppend the Items to it. When there's a new TableName (or when you're done), you ListAppend the Table to the output.

If you need any more details, please let us know.