cancel
Showing results for 
Search instead for 
Did you mean: 

Conversion of json file to Reltio Specific format

mithsrini
New Contributor II

Hello,
We are creating a data interface between snaplogic and reltio with a .json file.
Reltio expects the .json file to be in a specific format as below:
Notice the ‘new line’ i.e ‘\n’ inserted after every record of json data.
We tried scripts hook, regular expression with replaceAll…etc but haven’t been successful.
Any help in this regard would be appreciated.

We intend to achieve the following:

  1. Loop for json records.
  2. Fetch a record of json data.
    3 Append a new line / line feed / carriage return (and this should be visible in the RAW data preview too)
  3. Fetch another record of json data
  4. Insert this new record on a new line
  5. Store the updated file as .json

Any quick help shall be highly appreciated.

{"uri":"entities/aaa","type":"configuration/entityTypes/HCP","attributes":{"Name":[{"value":"NAME_DDD"}],"FirstName":[{"value":"FIRST_GGG"}],"LastName":[{"value":"LAST_GGG"}],"MiddleName":[{"value":"VAN"}],"Employment":[{"value":{"Title":[{"value":"TITLE_GGG"},{"value":"TITLE_GGG"}],"Summary":[{"value":"SUMMARY_GGG"}]}}],"Address":[{"value":{"City":[{"value":"CITY_GGG"}]}}],"Identifiers":[{"value":{"Type":[{"value":"TYPE2"}],"ID":[{"value":"ID1"}]}}]},"crosswalks":[{"value":"CROSSWALK_aaa","sourceTable":"SOURCE_TABLE_GGG","type":"configuration/sources/LNKD"}]},
{"uri":"entities/bbb","type":"configuration/entityTypes/HCP","attributes":{"Name":[{"value":"NAME_HHH"}],"FirstName":[{"value":"FIRST_HHH"}],"LastName":[{"value":"LAST_HHH"}],"MiddleName":[{"value":"VAN"}],"Employment":[{"value":{"Title":[{"value":"TITLE_HHH"},{"value":"TITLE_HHH"}],"Summary":[{"value":"SUMMARY_HHH"}]}}],"Address":[{"value":{"City":[{"value":"CITY_HHH"}]}}],"Identifiers":[{"value":{"Type":[{"value":"TYPE2"}],"ID":[{"value":"ID1"}]}}]},"crosswalks":[{"value":"CROSSWALK_bbb","sourceTable":"SOURCE_TABLE_HHH","type":"configuration/sources/LNKD"}]}
1 ACCEPTED SOLUTION

Try this. Basically, it still uses the JSON Formatter with “JSON Lines” but then added a Binary to Document with no conversion and a Mapper with Document input and Binary output views to replace the newline with comma-newline before writing back to file.

Example pipeline converting a CSV to the JSON format requested attached.

JSON_Lines_Create_File.zip (2.7 KB)

View solution in original post

5 REPLIES 5

koryknick
Employee
Employee

This sounds like you just need to use the JSON Formatter with “JSON Lines” option checked.

mithsrini
New Contributor II

Thank you @koryknick. I did use the “JSONLines” field, but the problem is that it doesn’t include a “,” (comma) after every json record. I need a way to append a comma, and a LINEFEED, so that the new jason record is written on the next line than just appending.

Try this. Basically, it still uses the JSON Formatter with “JSON Lines” but then added a Binary to Document with no conversion and a Mapper with Document input and Binary output views to replace the newline with comma-newline before writing back to file.

Example pipeline converting a CSV to the JSON format requested attached.

JSON_Lines_Create_File.zip (2.7 KB)

mithsrini
New Contributor II

Hi Kory,

Perfect! That was timely help.

Small issue, Its adding comma at the last line of the line also which is making in-valid json. Please suggest

Thanks,

Mithila JT