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
alopre...@apache.org
alopresto.apa...@gmail.com
PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4 BACE 3C6E F65B 2F7D EF69
> On Aug 15, 2017, at 8:59 AM, Charlie Frasure <charliefras...@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 <charliefras...@gmail.com
> <mailto:charliefras...@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 <marka...@hotmail.com
> <mailto:marka...@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 <alopre...@apache.org
> <mailto:alopre...@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
>> alopre...@apache.org <mailto:alopre...@apache.org>
>> alopresto.apa...@gmail.com <mailto:alopresto.apa...@gmail.com>
>> PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4 BACE 3C6E F65B 2F7D EF69
>>
>>> On Aug 14, 2017, at 6:57 PM, Charlie Frasure <charliefras...@gmail.com
>>> <mailto:charliefras...@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("~~", "'")},
>>> ....
>>> );
>>>
>>>
>>
>
>
signature.asc
Description: Message signed with OpenPGP using GPGMail
