Thanks guys,
Impressive mobile coding on your iPhone, Mark. :)
Let me provide sample data and desired result; that may help clarify my
issue.
We may receive JSON content like these:
{"Attribute1": "a", "Attribute2": "b"}
{"Attribute1": null, "Attribute2": "t"}
{"Attribute1": "z", "Attribute2": null}
I would like to create a SQL insert statement directly from each like so:
INSERT INTO schema.table1 (attribute_one, attribute_two) VALUES ('a','b');
INSERT INTO schema.table1 (attribute_one, attribute_two) VALUES (NULL,'t');
INSERT INTO schema.table1 (attribute_one, attribute_two) VALUES ('z',NULL);
Note that the NULLs are not the string "NULL" but a database NULL. I was
having trouble encapsulating the strings "a", "b", "t", and "z" in single
quotes as required by the database while leaving the text "NULL" without
any quotes.
I've tried variations of replaceEmpty(), append(), prepend(), changing the
behavior of nulls in the earlier SplitJSON processor, and even tried
embedding single quotes with a backslash escape:
${someJson:jsonPath("$.Attribute1"):isEmpty():ifElse("null",
'\'${someJson:jsonPath("$.Attribute1")}\'')}
It seems like I've reached a limit by having an argument ($.Attribute1) in
a function (jsonPath) as an argument of another function (ifElse). Given
that both functions require quotes around the arguments, I used single
quotes on the outer, and double quotes on the inner. This left me without
my single quotes for my SQL string values. Maybe on topic: How would you
nest three functions with the required quotes?
Best,
Charlie
On Mon, Aug 14, 2017 at 8:02 PM, Mark Payne <[email protected]> wrote:
> 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]> 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]
> *[email protected] <[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]>
> 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("~~", "'")},
> ....
> );
>
>
>
>
