Ask Your Question

Normalize the data in JSON [closed]

asked 2018-12-02 16:50:16 -0500

srinath_222 gravatar image

updated 2018-12-02 17:17:16 -0500


I have the input data in below format: { "kind": "insert", "table": "test", "columnnames": ["col1", "description"], "columntypes": ["integer", "character varying"], "columnvalues": [5, "test5"] }

I need the target data as: { "kind":"insert", "table":"test", "col1":"5", "description":"test5" }

I need to normalize the data getting key and value pairs from different arrays. I am trying with processors Field Pivoter, Field Merger and Field Flattener. But did not give the required results.

Please suggest me approach to normalize the data and load into MongoDB

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by srinath_222
close date 2018-12-06 14:11:16.384252

2 Answers

Sort by ยป oldest newest most voted

answered 2018-12-03 10:55:08 -0500

iamontheinet gravatar image


Use one of the scripting processors. For example, in Jython you'd write the following code:

for record in records:
    #Loop through column names array & create root level fields for each column with respective values from column values list 
    for index,col in enumerate(record.value['columnnames']):
      record.value[col] = record.value['columnvalues'][index]

    #Optionally delete original lists
    del record.value['columnnames']
    del record.value['columntypes']
    del record.value['columnvalues']

    # Write record to processor output
  except Exception as e:
    # Send record to error
    error.write(record, str(e))

This code will result in the following:

image description

Hope this helps.

Cheers, Dash

edit flag offensive delete link more

answered 2018-12-03 12:07:09 -0500

srinath_222 gravatar image

Cheers. That worked and I am able to transform the data.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2018-12-02 16:50:16 -0500

Seen: 59 times

Last updated: Dec 03 '18