I have data in a Mongo database that looks something like this:

{ "_id": {
"$oid": "111"
},
"linkedAccounts": [
{
"accountId": "123"
},
{
"accountId": "456"
},
]}

I am trying to get this data into a postgres table in the format:

id account_id
111 123
111 456

I have a job which uses a Mongo Input step and then a Json input step to read the data.

In the Fields tab in the Json Input I have added:

Name Path
ID $..$oid
account_id $..linkedAccounts[*].accountId

and, in the Table output, I have mapped the stream fields to the table fields.

When I run the job, I get the following error:

'The data structure is not the same inside the resource! We found 2 values for json path [$..linkedAccounts[*].accountId], which is different that the number retourned for path [$..$oid] (1 values). We MUST have the same number of values for all paths.'

However, if I remove all references to ID,$..$oid, I can successfully insert the account_id fields.

i.e. in the postgres table I have 2 rows

account_id
123
456

Can anyone advise as to how I can get both fields populated?

Or, is there altogether another approach to handling embedded documents?

Many thanks
Zimm