You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by Yuri Krysko <Yu...@mcdean.com> on 2017/06/27 12:42:18 UTC

QueryRecord processor

Hi Everyone,

Could you please provide an example of how to reference nested JSON fields in SQL statements when utilizing QueryRecord processor with Auro schema? I can successfully perform SELECT <some fields> FROM FLOWFILE but when I get to WHERE clause, no matter what I do, I get table does not exist <fieldname>

Thanks,
Yuri

________________________________

LEGAL DISCLAIMER: M.C. Dean, Inc. and its subsidiaries considers this e-mail and any files transmitted with it to be protected, proprietary or privileged information intended solely for the use of the named recipient(s). Any disclosure of this material or the information contained herein, in whole or in part, to anyone outside of the intended recipient or affiliates is strictly prohibited. M. C. Dean, Inc. accepts no liability for the content of this e-mail or for the consequences of any actions taken on the basis of the information contained in it, unless that information is subsequently confirmed in writing. Employees of M.C. Dean, Inc. are instructed not to infringe on any rights of the recipient; any such communication violates company policy. If you are not the intended recipient, any disclosure, copying, distribution, or action taken or omitted in reliance on this information is strictly prohibited by M.C. Dean, Inc.; please notify the sender immediately by return e-mail, delete this communication and destroy all copies.

Re: QueryRecord processor

Posted by Yuri Krysko <Yu...@mcdean.com>.
Ah, got it. Thanks much.

From: Mark Payne <ma...@hotmail.com>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Tuesday, June 27, 2017 at 8:46 AM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Subject: Re: QueryRecord processor

Hello Yuri,

If you want to reference the nested fields, you'll want to use the JsonPathReader instead of the JsonTreeReader
as your RecordReader. With JsonPathReader, you can specify any number of JSON Path expressions to extract
the fields of interest and give them names. Then you can reference these named fields in QueryRecord.

See [1] for more information in how to use the JsonPathReader.

Thanks
-Mark


[1] http://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-record-serialization-services-nar/1.3.0/org.apache.nifi.json.JsonPathReader/additionalDetails.html


On Jun 27, 2017, at 8:42 AM, Yuri Krysko <Yu...@mcdean.com>> wrote:

Hi Everyone,

Could you please provide an example of how to reference nested JSON fields in SQL statements when utilizing QueryRecord processor with Auro schema? I can successfully perform SELECT <some fields> FROM FLOWFILE but when I get to WHERE clause, no matter what I do, I get table does not exist <fieldname>

Thanks,
Yuri

________________________________

LEGAL DISCLAIMER: M.C. Dean, Inc. and its subsidiaries considers this e-mail and any files transmitted with it to be protected, proprietary or privileged information intended solely for the use of the named recipient(s). Any disclosure of this material or the information contained herein, in whole or in part, to anyone outside of the intended recipient or affiliates is strictly prohibited. M. C. Dean, Inc. accepts no liability for the content of this e-mail or for the consequences of any actions taken on the basis of the information contained in it, unless that information is subsequently confirmed in writing. Employees of M.C. Dean, Inc. are instructed not to infringe on any rights of the recipient; any such communication violates company policy. If you are not the intended recipient, any disclosure, copying, distribution, or action taken or omitted in reliance on this information is strictly prohibited by M.C. Dean, Inc.; please notify the sender immediately by return e-mail, delete this communication and destroy all copies.


________________________________

LEGAL DISCLAIMER: M.C. Dean, Inc. and its subsidiaries considers this e-mail and any files transmitted with it to be protected, proprietary or privileged information intended solely for the use of the named recipient(s). Any disclosure of this material or the information contained herein, in whole or in part, to anyone outside of the intended recipient or affiliates is strictly prohibited. M. C. Dean, Inc. accepts no liability for the content of this e-mail or for the consequences of any actions taken on the basis of the information contained in it, unless that information is subsequently confirmed in writing. Employees of M.C. Dean, Inc. are instructed not to infringe on any rights of the recipient; any such communication violates company policy. If you are not the intended recipient, any disclosure, copying, distribution, or action taken or omitted in reliance on this information is strictly prohibited by M.C. Dean, Inc.; please notify the sender immediately by return e-mail, delete this communication and destroy all copies.

Re: QueryRecord processor

Posted by Mark Payne <ma...@hotmail.com>.
Hello Yuri,

If you want to reference the nested fields, you'll want to use the JsonPathReader instead of the JsonTreeReader
as your RecordReader. With JsonPathReader, you can specify any number of JSON Path expressions to extract
the fields of interest and give them names. Then you can reference these named fields in QueryRecord.

See [1] for more information in how to use the JsonPathReader.

Thanks
-Mark


[1] http://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-record-serialization-services-nar/1.3.0/org.apache.nifi.json.JsonPathReader/additionalDetails.html


On Jun 27, 2017, at 8:42 AM, Yuri Krysko <Yu...@mcdean.com>> wrote:

Hi Everyone,

Could you please provide an example of how to reference nested JSON fields in SQL statements when utilizing QueryRecord processor with Auro schema? I can successfully perform SELECT <some fields> FROM FLOWFILE but when I get to WHERE clause, no matter what I do, I get table does not exist <fieldname>

Thanks,
Yuri

________________________________

LEGAL DISCLAIMER: M.C. Dean, Inc. and its subsidiaries considers this e-mail and any files transmitted with it to be protected, proprietary or privileged information intended solely for the use of the named recipient(s). Any disclosure of this material or the information contained herein, in whole or in part, to anyone outside of the intended recipient or affiliates is strictly prohibited. M. C. Dean, Inc. accepts no liability for the content of this e-mail or for the consequences of any actions taken on the basis of the information contained in it, unless that information is subsequently confirmed in writing. Employees of M.C. Dean, Inc. are instructed not to infringe on any rights of the recipient; any such communication violates company policy. If you are not the intended recipient, any disclosure, copying, distribution, or action taken or omitted in reliance on this information is strictly prohibited by M.C. Dean, Inc.; please notify the sender immediately by return e-mail, delete this communication and destroy all copies.