Solved

How to generate nested JSON

  • 21 July 2022
  • 2 replies
  • 515 views
S
Rank

Hi,

I want to generate multiple nested json array from multiple tables (in this case from table HEADER and table DETAIL) and I want DETAIL’s records to fetch under 1 json array objects like this example:

[{
        "ID_HEADER": "header01",
        "DETAIL": [{
                "ID_DETAIL": "detail1",
                "VALUE": 21521,
                "UNIT": EA
            }, {
                "ID_DETAIL": "detail2",
                "VALUE": 24,
                "UNIT": EA
            }, {
                "ID_DETAIL": "detail3",
                "VALUE": 25135,
                "UNIT": EA
            }, {
                "ID_DETAIL": "detail4",
                "VALUE": 135234,
                "UNIT": EA
            }
        ]
    }, {
        "ID_HEADER": "header02",
        "DETAIL": [{
                "ID_DETAIL": "detail1",
                "VALUE": 23,
                "UNIT": EA
            }, {
                "ID_DETAIL": "detail2",
                "VALUE": 353,
                "UNIT": EA
            }
        ]
    }
]

 

I already try to create the pipeline to get HEADER table records and then using /ID_HEADER to lookup from DETAIL table and then try to fetch DETAIL table’s record into /DETAIL list-maps using Field Zip stage but it's only combine 2 fields and I need to combine more than 2 fields.

Am I using wrong processor stages or is there any way to generate mupliple nested json array? Can someone assist me on these please.

Thank you.

icon

Best answer by saleempothiwala 21 July 2022, 19:52

View original

2 replies

saleempothiwala
Rank
Userlevel 4
Badge

hi @sevtiandy 

 

I think you are using JDBC lookup processor to get the detail record. By nature, jdbc lookup processor will return 1 lookup value from source against a key. 

 

A simple way of creating a master detail nested json could be to use JDBC query consumer as the Origin and write a joined query. Use one of the code evaluators to then loop through to generate nested json. Be careful, if all the master records are not part of the same batch then you will get multiple records for same master.

 

use something like this with json Evaluator: https://stackoverflow.com/questions/44178371/converting-csv-to-nested-json-in-javascript

S
Rank

hi @saleempothiwala 

At first I wrote a joined query on my jdbc consumer origin, but like what you said to be careful when it’s not in the same batch and it did happen when the detail’s records exceeded my Max Batch Size (the detail’s records split into few batches) that’s why I try to use jdbc lookup to get the detail table’s records so i can set Multiple Values Behavior to stored all values from detail’s records as a list on each ID_HEADER (header’s key) records.
 

I will try to use code evaluator as your suggestion to generate nested json although I’m not familiar with coding but maybe I’ll  try to use javascript evaluator to follow just like your example.
 

Thank you for your reply.

Reply


Terms & Conditions