JSON Filtering Question

General
1

Hello all

I'm sure someone will be able to answer this...it's been driving me mad all weekend!

I've taken a CSV file and brought it into a JSON file object for use 'locally' within a future Dashboard UI operation, I am however struggling to only forward on specific properties and values of the message.

The CSV contains multiple rows. This is stored in a JSON file. It's then outputted and split by row into individual messages.

I then want to only forward specific columns / fields / objects (apologies if I haven't got the terminology correct) onwards in the flow.

I've been attempting to use various JSON filter nodes. It seems straight forward, enter the field names separated by commas and only these will be forward in the payload.

node-red-contrib-jsonfilter

Instead of this occurring, I receive a message regarding:

The following field in the JSON filter configuration does not have a match in the data headers.

My efforts are below, can anyone help perform, what I'm sure, is a simple process that is doing my head in! :slight_smile:

Update: I note that taking a look at one of the msgs out of the JSON converter shows, what I think is an incomplete formatted JSON string. Invalid character? Length? I see ... at the end of the string. Is this what is upsetting the JSON filter...I bet it is. Any reasons why? Each message seems to have a slightly different length but are all ending, not with a curly bracket but ...

{"recordkey":"0","recordtimestamp":"2018-07-30 00:00:00.000","arrivalsite":"526524024","siterollup":"1","busunitkey":"1","calltypekey":"19","numreceived":"105","numreceivedreq":"0","numreceivednetin":"0","numansweredprim":"94","numansweredover":"0","numabandoned":"11","numredirectedoutscope":"0","numredirectednetout":"0","ansunderthreshold":"76","abnunderthreshold":"7","totanswaittime":"4734","totabanwaittime":"1192","totredirwaittime":"0","totwaittime":"5867","answered1":"34","answered2":"16","answered3":"6","answered4":"5","answered5":"33","abandoned1":"2","abandoned2":"1","abandoned3":"2","abandoned4":"0","abandoned5":"6","waitcountbegin":"0","waitcountend":"0","maxanswaittime":"349","maxabanwaittime":"366","maxredirwaittime":"0","maxcountwaiting":"3","mincountwaiting":"0","maxcountoverflows":"0","mincountoverflows":"0","numoverflows":"0","usersinvolvedtime":"10882","additionalhandledcount":"0","totcontacttime":"18238","additionalcontactcount":"0","totdefertime":"0","defercount":"0"...

Many, Many thanks.

Mo

Flow:

[{"id":"fa812d1.7a10cd","type":"inject","z":"bcdf6cac.32951","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":198.8984375,"y":1976.9571151733398,"wires":[["db6b7e74.d3301"]]},{"id":"db6b7e74.d3301","type":"DataOut","z":"bcdf6cac.32951","collection":"50099a5.5a1f164","name":"CallTypeDaily","topic":"CallTypeDaily","error":true,"x":465.8828125,"y":1986.25390625,"wires":[["9b669ffd.78f5c"]]},{"id":"9b669ffd.78f5c","type":"split","z":"bcdf6cac.32951","name":"","splt":"\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":747.8907623291016,"y":1985.8327541351318,"wires":[["c6aa3e96.6c84e","ef73a6f6.a01ec8"]]},{"id":"c6aa3e96.6c84e","type":"json","z":"bcdf6cac.32951","name":"","property":"payload","action":"","pretty":false,"x":882.8827972412109,"y":2058.21492767334,"wires":[["11908911.8dbff7"]]},{"id":"11908911.8dbff7","type":"JSONFilter","z":"bcdf6cac.32951","name":"Filter","fields":"recordtimestamp,numreceived","expiry":5,"x":1089.88671875,"y":2092.46484375,"wires":[[]]},{"id":"50099a5.5a1f164","type":"json-db-collection","z":"","name":"CallTypeDaily","collection":"CallTypeDaily","save":true}]

2

The JSON node you are using is set to convert FROM a JSON string TO a javascript object.

You then feed it through a node designed to take in JSON and export TO a filtered Javascript Object.

If you can post your original CSV someone can probably suggest a more appropriate way of achieving your aim.

3

My favorite way to restructure JS objects is to use a change node and a JSONata expression -- to retrieve just the 3 properties you want, this expression should work:

payload.{
    "recordtimestamp": recordtimestamp,
    "numreceived": $number(numreceived),
    "expiry": expiry
}

The JSONata expression language is a bit complex, but very powerful -- it is well worth the couple hours it takes to read through the whole jsonata.org site. There is even an expression "tester" built into the change node. When you pick the J: "expression" option and click the ... button on the right, you'll see this page:

image
image.png600Ă—737 25.7 KB

From the debug sidebar, you can use the "copy value" button to grab any object as a JSON string, paste that into the left pane of the tester following "payload": in place of the "hello world" string -- then as you type your expression in the top editor, you will see the output in the right pane in real time... very nice.

2 Likes
4

How come I never noticed this before - brilliant tool :slight_smile: