OK. I finally got this to work, but the method I used seems kind of overlong and hacky. First I took the input and ran it through this:
JSON.stringify(jsonPath($, "entity.feed.entry[*].content['$']")).replaceAll('\\n','').replaceAll('\\','').replaceAll('"{','{').replaceAll('}"','}')
This cleaned up the string to look like clean JSON minus the \n, escape and extra quote characters.
After this I turned it into a document and read it back in and used a JSON parser.
This worked, but I’m not really happy with the method. Is there any sort of clean function that takes control characters and escapes and just scrubs them? I’ve seen payloads in the past that come in a single field in a REST call and it seems like I’m doing a lot of extra and unnecessary work if I have to write it to disk just to read it back in.
Realized I can skip the File Writing by doing a JSON.parse() on the whole thing. Still seems like there should be a better way.
JSON.parse(JSON.stringify(jsonPath($, "entity.feed.entry[*].content['$']")).replaceAll('\\n','').replaceAll('\\','').replaceAll('"{','{').replaceAll('}"','}'))
If your Oracle table has columns whose names contain a ‘$’ (or any other special character) in it, here’s how the execute query needs to look like:
Query: select “USER$ID” from testtable1
Note: Expression should not be enabled on the “Oracle Execute” snap
Thank you! That did the trick.
LF's in CSV to result in a Multi-Line Cell in Excel | SnapLogic - Integration Nation - 21833
