One Star

Convert relational data to JSON

Hi,
I have the basics of this nailed down but there are things I can't get my head around.
We need to convert some relational data into json format for sending to an API as an HTTP POST request. 
Example source data (CSV)
COUNTRY,FILM,STAR_RATING,FILM_PAGE_URL
UK,FILM 1,3,http://test.co.uk
AU,FILM 2,4,http://test.com.au

Required output 
Here there are multiple films in one payload. This example just has two.


  
So far we have the following components (I have replaced the tRest component that will ultimately be used with a file output to troubleshoot the end JSON).
In the tWriteJSON component we have the following tree set up
And the output schema is just set to "String"
This is mostly working but the end file looks like below. I can't work out how to get rid of the repeating attribute names "string" and the root "data". Maybe this isn't possible or I just need to do a find and replace to take these out?
I also don't want all the escape characters "\" that seem to have been inserted. The tRest component only requires the " to be escaped.
{  
   "data":
}

I would appreciated any input to solve these formatting issues, or indeed some guidance on better best practice for this kind of scenario.
Thanks,
b/
1 REPLY

Re: Convert relational data to JSON

Hi Bisquite,
Can you share the screen shot of the mapping and how you are grouping multiple rows into JSON array ?
I tried this and it worked fine but, outtput is a json/row.
There is a option in tReadJsonFields basic settings to exclude rootTag.