Hello Vikram.
I tried using your data on Drill 1.9.0
[test@test-01 ~]# cat mongo_test.json
{
"_id" : " some_value ",
"_updated" : " some_value ",
"brands" : [
" some_value "
],
"_created" : " some_value ",
"_status" : " some_value ",
"metadata" : {
"username" : [
{
"site_id" : " some_value "
}
],
"status" : [1,3,4,5,6,7]
}
}
/* To extract username value */
/* Note that the field metadata is enclosed in back ticks in the SQL */
0: jdbc:drill:schema=dfs.tmp> select sub_query.t.username from ( select
`metadata` t from `mongo_test.json`) sub_query;
+-------------------------------+
| EXPR$0 |
+-------------------------------+
| [{"site_id":" some_value "}] |
+-------------------------------+
1 row selected (0.223 seconds)
/* To extract site_id value */
0: jdbc:drill:schema=dfs.tmp> select sub_query.t.username.site_id from (
select `metadata` t from `mongo_test.json`) sub_query;
+---------------+
| EXPR$0 |
+---------------+
| some_value |
+---------------+
1 row selected (0.216 seconds)
Thanks,
Khurram
On Thu, Oct 27, 2016 at 11:53 AM, Vikram H. Taori <[email protected]>
wrote:
> Dears,
>
>
>
> Any reply/help over this below issue ??
>
>
>
> ------------------------------------------------------------
> ------------------------------------------
>
> *Vikram Taori* | *InfoCepts *| www.infocepts.com
>
> Off: +91 712 224 5867 Ext 8338, +1-301-769-6212 Ext 8338, Mob: +91 837 443
> 0420
>
> ------------------------------------------------------------
> ------------------------------------------
>
>
>
> *From:* Vikram H. Taori
> *Sent:* 25 October 2016 17:16
> *To:* '[email protected]'
> *Subject:* Drill Over MongoDB | Unable to access filelds from complex
> Json Object
>
>
>
> Dears,
>
>
>
> We are using Drill 1.8.0 over MongoDB 3.2.10.
>
>
>
> Here we are accessing json data (using JDBC connection via Squirrel).
>
>
>
> *For Simple json dataset:*
>
>
>
>
>
> Accessing using Squirrel:
>
>
>
>
>
> *For Complex json data:*
>
>
>
>
>
> {
>
> "_id" : " *some_value* ",
>
> "_updated" : " *some_value* ",
>
> "brands" : [
>
> " *some_value* "
>
> ],
>
> "_created" : " *some_value *",
>
> "_status" : " *some_value* ",
>
> "metadata" : {
>
> "username" : [
>
> {
>
> "site_id" : "* some_value* "
>
> }
>
> ],
>
> "status" : [
>
>
>
>
>
> Query like “select * from mongo.test.`member`;”
>
> gives *error “Error: SYSTEM ERROR: IllegalArgumentException: You tried to
> write a Float8 type when you are using a ValueWriter of type
> NullableVarCharWriterImpl.”*
>
>
>
> For selected *first level* of columns, it *runs successfully*
>
>
>
> *Our Query:*
>
>
>
> *How do we access fields from complex json object in drill??*
>
>
>
> Accessing “site_id” under “username”:
>
>
>
> I did google and found this approach
>
>
>
> Tried: “ select _id, _updated, brands[0], _created, _*status,
> metadata.username[0].site*_id from mongo.test.`member` ”
>
>
>
> Gives error: “Error: PARSE ERROR: Encountered ", metadata" at line 1,
> column 51”
> This e-mail and any attachments are confidential and intended solely for
> the addressee and may also be privileged or exempt from disclosure under
> applicable law. If you are not the addressee, or have received this e-mail
> in error, please notify the sender immediately, delete it from your system
> and do not copy, disclose or otherwise act upon any part of this e-mail or
> its attachments. Internet e-mails are not necessarily secure and are
> susceptible to change. InfoCepts does not accept responsibility for changes
> made to this message after it was sent. Whilst all reasonable care has been
> taken to avoid the transmission of viruses, it is the responsibility of the
> recipient to ensure that the onward transmission, opening or use of this
> message and any attachments will not adversely affect its systems or data.
> No responsibility is accepted by InfoCepts in this regard and the recipient
> should carry out such virus and other checks as it considers appropriate.
> InfoCepts does not also accept responsibility for the improper or
> incomplete transmission of the information contained in this communication
> nor for any delay in its receipt or damage to your system and does not
> guarantee that the integrity of this communication has been maintained.
> Replies to this e-mail may be monitored by InfoCepts for operational or
> business reasons.
>
