transform json to table with get multiple parent in every child element with pentaho

Question

I have API input with json format like,

{
    "report": {
        "accounts": [
            {
                "subheader": "(1-10005)",
                "beginning_balance": {
                    "date": "29/12/2022",
                    "balance": 0
                },
                "content": [
                    {
                        "transaction": {
                            "date": "31/12/2022",
                            "transaction_type": "Bank Deposit",
                            "number": "10002",
                            "description": "description 1",
                            "debit": " 1.000.000,00",
                            "credit": " 0,00",
                            "balance": " 1.000.000,00"
                        }
                    },
                    {
                        "transaction": {
                            "date": "31/12/2022",
                            "transaction_type": "Bank Withdrawal",
                            "number": "10002",
                            "description": "description 2",
                            "debit": " 0,00",
                            "credit": " 300.000,00",
                            "balance": " 700.000,00"
                        }
                    }
                ]
            }
        ]
    }
}

and i try to load to database table with pentaho data integration with result like,

in pentaho transformation i use tools

but getting errors like

thanks for the help..

Answer 1

First get the block "$..content" and the fields "subheader", "$..beginning_balance.date" and "$..beginning_balance.balance" out with a first json step, and then feed the result into a new json step where you divide the content block further in the specific fields