Ask Your Question
0

Splitting a json value that contains a csv

asked 2018-03-22 12:06:41 -0500

Nightwatch gravatar image

updated 2018-03-22 14:39:41 -0500

jeff gravatar image

Hi, I'm trying to figure out how to parse a Json that contains a value that is a CSV and split every line to send multiple POST with a http destination (REST API resource). The pipeline now is: ORIGIN: http server in Streamset appliance that receive a Json with a a base64 value in it (it works) PROCESSOR: decode base64 field (it works)

now I have this result in json (written in fs local for debug purposes)

[{
  "id":"001",
  "DPIYesOrNo":1,
  "typeDPI":"100",
  "decoded_field":"SERIAL NUMBER,MAC ADDRESS,FORM FACTOR,TIMECREATE,NUMBER ITEM\r\n17006492100005,DC:AB:B2:6A:34:A4,1,28/11/2017 22:25:02,43039167\r\n17006492100006,F5:57:C8:D1:62:50,1,28/11/2017 22:16:18,43039167\r\n17006492100007,FF:18:C8:DE:92:4A,1,28/11/2017 \r\n"
  .
  .
  .
  .
}]

I would like to split "decoded_field" and obtain multiple json and send them via http client destination

{
    "id": "001",
    DPIYesOrNo: 1,
    typeDPI: "100",
    data: [
        {
            SERIAL NUMBER: "xxxxxx",
            NAME: "Imported",
            macAddress: "MAC ADDRESS",
        },
        {},
        {},
        ……..
    ]
}
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-03-23 16:06:49 -0500

mstang gravatar image

updated 2018-03-23 16:10:08 -0500

You can use a javascript evaluator to split your csv field into a list field then use a field pivoter on the list field to create separate records. Quick and dirty the JS looks like this (you may need to adjust for your field names, this is what I got when I used your json example as a one line text file origin and ran it through a json parser making it's root /json):

records[i].value['decoded_field_list'] = records[i].value['json'][0]['decoded_field'].split('\r\n')

This will give you an extra first record that is your headers so you'll probably want to remove the first element of the list.

edit flag offensive delete link more

Comments

Thanks mstang! It works like a charm!

Nightwatch gravatar imageNightwatch ( 2018-03-30 03:06:33 -0500 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-03-22 12:06:41 -0500

Seen: 20 times

Last updated: Mar 23