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>
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> 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> 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):toStrin
>> g()})})}
>>
>> 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
>> alopre...@apache.org
>> *alopresto.apa...@gmail.com <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>
>> 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("~~", "'")},
>> ....
>> );
>>
>>
>>
>>
>
