Hi all,
We found that the ConvertJSONToSQL did not give us the control we wanted
over the SQL being created from an existing JSON object, so we built our
own query using the UpdateAttribute processor. In doing so, we ran into a
problem setting the SQL value from the JSON data because of quotations.
The ifElse function required a quote, as did the embedded path to the JSON
value. This left no way to wrap the SQL text with single quotes, and I
have not found documentation of a character that would allow me to escape
another set of quotes.
A basic working example is pasted below, but I had to ask if there is a
better way.
Best,
Charlie
INSERT INTO schema.table1
(attribute_one, attribute_two, ...)
VALUES (
${someJson:jsonPath("$.Attribute1"):isEmpty():ifElse("null",
'~~${someJson:jsonPath("$.Attribute1")}~~'):replace("~~", "'")},
${someJson:jsonPath("$.Attribute2"):isEmpty():ifElse("null",
'~~${someJson:jsonPath("$.Attribute2")}~~'):replace("~~", "'")},
....
);
