We receive the data from an API which can send it in ND Json/newline delimited Json format something like below. Does Matallion has a way to process these type of records to read

Matillion ETL
1

We receive the data from an API which can send it in ND Json/newline delimited Json format something like below. Does Matallion has a way to process these type of records to read as is and process/load them in Redshift ? This is not a regular JSON.

{"Employee":"123","EmployeeName":"X"}

{"Employee":"345","EmployeeName":"Y"}

 

2

Hi @PK535525​,

Redshift needs a JSONPaths helper for most real JSON files, but your example is one it can handle without that.

The file is not real JSON, because it's not wrapped in an outer array. However it is valid JSONL (confusingly sometimes also called LJSON or NDJSON), which is helpful for Redshift. The key things in JSONL being:

  • Every individual line is a valid JSON document
  • There are no newlines anywhere except between the JSON documents

So in Matillion ETL for Redshift you need an S3 Load component, and just switch it to JSON mode like the below screenshot.

0694G00000GGoJBQA1_0D74G000007jhRhSAI
0694G00000GGoJBQA1_0D74G000007jhRhSAI512×111 7.83 KB

With your file I also had to set the JSON Layout to auto ignorecase because the fieldnames are initcap. Redshift tries to map the keys in the JSON objects to column names. Without "ignorecase" it finds "Employee" but won't map it to a column named "employee", so it creates records but silently does not actually set the column values.

Best regards,

Ian