Charlie,
There is also a replaceEmpty() function that I think should simplify this:
INSERT INTO schema.table1
(attribute_one, attribute_two, ...)
VALUES (
${someJson:jsonPath("$.Attribute1"):replaceEmpty("null")},
${someJson:jsonPath("$.Attribute2"):replaceEmpty("null")},
....
);
Thanks
-Mark
Sent from my iPhone
On Aug 14, 2017, at 7:30 PM, Andy LoPresto
<[email protected]<mailto:[email protected]>> wrote:
Hi Charlie,
I may be misunderstanding your issue, but have you tried escapeJson() [1]? You
can chain it onto a String expression, and it will escape a single quote to \’.
You can also use unescapeJson() [2] if you need to provide an escaped quote and
have it be interpreted by the next step.
I have a feeling that doesn’t quite answer your question, so maybe literal()
[3] is what you are looking for. For example, I have an expression below which
generates a random number that is either 0 or 1, and prints ‘zero’ for 0, but
‘1’ for 1:
${random():mod(2):lt(1):ifElse('zero', ${literal(${literal(1):toString()})})}
I hope one of these helps. If not, please write back and hopefully someone who
understands the question a bit better will be able to chime in.
[1]
https://nifi.apache.org/docs/nifi-docs/html/expression-language-guide.html#escapejson
[2]
https://nifi.apache.org/docs/nifi-docs/html/expression-language-guide.html#unescapejson
[3]
https://nifi.apache.org/docs/nifi-docs/html/expression-language-guide.html#literal
Andy LoPresto
[email protected]<mailto:[email protected]>
[email protected]<mailto:[email protected]>
PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4 BACE 3C6E F65B 2F7D EF69
On Aug 14, 2017, at 6:57 PM, Charlie Frasure
<[email protected]<mailto:[email protected]>> wrote:
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("~~", "'")},
....
);
