Talend- Need to extract data from JSON (JSON array) and load it to Oracle DB

Question

I have a Talend Job that receives a JSON(JSON format below) from a route. I need to extract data from JSON and load it to Oracle DB table.

Job

JSON format:

{
    "data": [
        {
            "name": "FRSC-01",
            "recordnum": "01",
            "Expense1": "100",
            "Expense2": "7265",
            "Expense3": "9000"
        },
        {
            "name": "FRSC-02",
            "recordnum": "",
            "Expense1": "200",
            "Expense2": "6000",
            "Expense3": "9000"
        },
        {
            "name": "FRSC-03",
            "recordnum": "03",
            "Expense1": "200",
            "Expense2": "7000",
            "Expense3": "8000"
        }
    ]
}

Answer 1

You can use tExtractJsonFields component to extract data from your json.
Define a schema with the columns you want from the json (name, recordNum, Expense1, Expense2, Expense3), set loop jsonpath query to "$.data[*]", and then for each column set the jsonpath expression like so:
name => "name"
recordNum => "recordNum"
...
And then just use a tMap to map the columns to your target table in the tOracleOutput component.