nifi-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andy LoPresto <alopre...@apache.org>
Subject Re: Expression language escape character?
Date Tue, 15 Aug 2017 14:24:02 GMT
Charlie,

I’m glad you found a solution that works for you. As you point out, the final expression
you have still has a number of repeated sequences, and replacing some with literal() evaluations
isn’t much shorter. If possible, could you please document exactly how the ConvertJsonToSql
processor was not meeting your needs? Hopefully we can fix whatever was blocking you and make
this an improved experience moving forward.

Andy LoPresto
alopresto@apache.org
alopresto.apache@gmail.com
PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4  BACE 3C6E F65B 2F7D EF69

> On Aug 15, 2017, at 8:59 AM, Charlie Frasure <charliefrasure@gmail.com> wrote:
> 
> I found a solution that I like better than replacing tildes with single quotes.  I had
made the assumption that the expression language function arguments had to be quoted, but
found that isn't true, so the working string I am using looks like this:
> 
> INSERT INTO schema1.table1
>   (attribute_one, attribute_two, ...)
> VALUES (
> 	${someJson:jsonPath("$.Attribute1"):isEmpty():ifElse("NULL", ${someJson:jsonPath("$.Attribute1"):trim():prepend("'"):append("'")})},
> 	${someJson:jsonPath("$.Attribute2"):isEmpty():ifElse("NULL", ${someJson:jsonPath("$.Attribute2"):trim():prepend("'"):append("'")})},
> 	...
> );
> 
> Numeric attributes can be updated without including the quotes, and now we can tune the
SQL as desired.
> 
> I'd still like to know if there is an escape character that would allow us to embed single
quotes as text within a function.  Andy's suggestion of literal() with a hex or decimal value
would probably work, or HTML encoding of the character, then back again, but those options
seem just as much of a hack as the replace I was doing.
> 
> Any more ideas?
> 
> Best,
> Charlie
> 
> 
> On Mon, Aug 14, 2017 at 9:50 PM, Charlie Frasure <charliefrasure@gmail.com <mailto:charliefrasure@gmail.com>>
wrote:
> 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 <markap14@hotmail.com <mailto:markap14@hotmail.com>>
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 <alopresto@apache.org <mailto:alopresto@apache.org>>
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
<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
<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
<https://nifi.apache.org/docs/nifi-docs/html/expression-language-guide.html#literal>
>> 
>> Andy LoPresto
>> alopresto@apache.org <mailto:alopresto@apache.org>
>> alopresto.apache@gmail.com <mailto:alopresto.apache@gmail.com>
>> PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4  BACE 3C6E F65B 2F7D EF69
>> 
>>> On Aug 14, 2017, at 6:57 PM, Charlie Frasure <charliefrasure@gmail.com <mailto:charliefrasure@gmail.com>>
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("~~",
"'")},
>>> ....
>>> );
>>> 
>>> 
>> 
> 
> 


Mime
View raw message