Ask Your Question
0

Normalize the data in JSON [closed]

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

srinath_222 gravatar image

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

Hi,

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
0

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

iamontheinet gravatar image

Hi!

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

for record in records:
  try:    
    #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
    output.write(record)
  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
0

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

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

Stats

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

Seen: 17 times

Last updated: Dec 03