How to best combine/map values from separate inputs?

[acesario]

I’m trying to produce output containing Value and Item ID from separate files.

I have two input files for this, with different naming for the fields For example,
Transaction Name=TRANSACTION
First Name=FNAME
LastName=LNAME

In addition, one file contains the id, the other contains the value. I need to combine these into an output with Instance (text value) and the ID value. I have a gate setup to create a doc with input0 and input1 as shown below.

Input0
“Instance”:“Transaction Name”
“ID”:“bae77”

Input1
“TRANSACTION”:“AC00014623”

What I want this to look like is:

{TextValue":“AC00014623”
“Item”:{“id”:“bae77”}

I have about 40 of these pairs, slightly different in each file.

Any recommendations or ideas?

[acesario]

@ptaylor Apologies that I only put the input files in the attachment. Here they are for your reference:
Input file 1
[{“TRANSACTION”: “AC00014623”,
“FIRST_NAME”: “Jorge”,
“LAST_NAME”: “Whoever”}]

Input file 2:
[{“TRANSACTION_WID”: “bae771d54a9d018123ac687fea12800c”,
“FIRST_NAME_WID”: “bae771d54a9d01f0a3b4687fea12810c”,
“LAST_NAME_WID”: “bae771d54a9d011c59ba687fea12820c”
}]

The use case at this point is basically an APPEND the WID values to each input1 document. File 2 does not change. File 1 may have many rows.

Note: I was able to accomplish this via hard coding the values to variables in a mapper, but the reference id’s may change from time to time, and it is better practice to pull from a file, or web service call. I hope this helps explain what I’m trying to accomplish.

[ptaylor]

Sorry, I’m still not following. There are no values in common between the two files, so how would you expect a join to work?

[acesario]

@ptaylor Correct. There are currently no matched fields. In other tools, I have been able to append one file with another.

[koryknick]

@acesario - From what you are describing, I think @pataylor provided the correct solution with the Join snap using a 1=1 condition. Assuming that file 1 has an arbitrary number of records, and file 2 has only 1 record, it would successfully append the fields in file 2 for each record in file 1. This would match what you did with the hardcoded values in the Mapper that you describe earlier.

[ptaylor · Accepted solution]

@acesario I think what I’m understanding is that one of your files might have an array with more than one element, but the default parsing turns each element into a separate output document. You can change that by setting “Process array” to false on the JSON Parser. That will produce a single output document whose data is the full array. Before you can feed that into the Join, you’ll have to use a Mapper to map the array to the field of an object. I’ve attached a sample pipeline.

Community 8107_2020_08_27.slp (9.8 KB)

Here are the inputs…

fileA.json:

[
    {
        "file": "A"
    }
]

fileB.json:

[
    {
        "file": "B",
        "id": 1
    },
    {
        "file": "B",
        "id": 2
    }
]

Output of the Join:

[
  {
    "fileA": {
      "file": "A"
    },
    "fileB": [
      {
        "file": "B",
        "id": 1
      },
      {
        "file": "B",
        "id": 2
      }
    ]
  }
]

That combines all of the data from both input files into a single document. You can modify the Mappers to move things to the right places.