Thank you Josef, That works for the empty string values turning to null in the numeric case. I can probably change my use case to allow null as a replacement for false in the Booleans case.
Steve Hindmarch From: [email protected] <[email protected]> Sent: 18 July 2022 06:44 To: [email protected]; Hindmarch,SJ,Stephen,VIR R <[email protected]> Subject: Re: Update records with literal null, true or false values. Hi Stephen Not sure whether I understood you correct and if I really remember it correct, but the UpdateRecord processor below should produce a NULL value if the string is “” (empty). We don’t use it anymore, so I’m not 100% whether it still works or does what you want. And yes it’s not straight forward ;-). [Graphical user interface, text, application, email Description automatically generated] Cheers Josef From: "stephen.hindmarch.bt.com via users" <[email protected]<mailto:[email protected]>> Reply to: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>>, "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Date: Friday, 15 July 2022 at 13:13 To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Subject: Update records with literal null, true or false values. Hi all, I have been looking at a case where some records have all fields presented as strings, and I need to turn the numeric or boolean values into their native types. I can do most of this with Jolt, but in the case where the value is missing I have a problem. Say I have these records. [ {"latitude":"1.0","longitude":"-1.0","user":{"name":"alice","id":"12345671","has_cover":"true"},"vehicle":{"id":"AB123DE"}}, {"latitude":"1.0","longitude":"-1.0","user":{"name":"bob","id":"12345672","has_cover":"false"},"vehicle":{"id":"AB123DE"}}, {"latitude":"","longitude":"","user":{"name":"chuck","id":"","has_cover":"flargh"},"vehicle":{"id":""}} ] I can use “modify-overwrite” to turn the coordinates into doubles, the Booleans into true/false, and the user ID into a numeric. But this fails for Chuck’s record as Jolt ignores the empty string or none-truthy strings. The result I get is like this. [ {"latitude":1.0,"longitude":-1.0,"user":{"name":"alice","id":12345671,"has_cover":true},"vehicle":{"id":"AB123DE"}}, {"latitude":1.0,"longitude":-1.0,"user":{"name":"bob","id":12345672,"has_cover":false},"vehicle":{"id":"AB123DE"}}, {"latitude":"","longitude":"","user":{"name":"chuck","id":"","has_cover":"flargh"},"vehicle":{"id":""}} ] But what I really want, in order to conform to my Avro schema, is more like this. [ {"latitude":1.0,"longitude":-1.0,"user":{"name":"alice","id":12345671,"has_cover":true},"vehicle":{"id":"AB123DE"}}, {"latitude":1.0,"longitude":-1.0,"user":{"name":"bob","id":12345672,"has_cover":false},"vehicle":{"id":"AB123DE"}}, {"latitude":null,"longitude":null,"user":{"name":"chuck","id":null,"has_cover":false},"vehicle":{"id":""}} ] I looked at UpdateRecord and EvaluteJSONPath, but I cannot see a way to return a literal null, true or false. I have resorted to using some ReplaceTexts which can find and replace some of the errant values, but struggles with distinguishing between the user ID, which has to be numeric, and the vehicle ID, which needs to stay as a string. And global find and replace on text seems like a coarse instrument when the content is already neatly in records. Can anyone suggest a better solution? Thanks. Steve Hindmarch
