Convert the MongoDB Object Id to String in Drill

Posted on by By admin, in Databases | 0

  1. Mongo Database is document-oriented database. Unlike the relational database, In the Mongo database, data will be stored in JSON-like documents. MongoDB stores data in flexible, JSON-like documents, meaning fields can vary from document to document and data structure can be changed over time. The primary key of each document in the collection will be stored in Object format.

    2. Object format

  2. Using Apache Drill we can extract the data from MongoDB in a simple SQL query. Apache Drill supports a variety of NO-SQL databases like MongoDB, HBase etc..

    3. Make data easy with Helical Insight.
    Helical Insight is the world’s best open source business intelligence tool.

    Grab The 30 Days Free Trail

  3. Create a mongo storage plugin to connect to the MongoDB and enter the MongoDB credentials like host, username, password
  4. Write a simple Select query from a collection

    5. Query: select _id, FirstName from mongo.Sample.Person

    Here I have selected a primary key id column and first name column from the collection Person

    Output:
    Selected Query

  5. In the above screenshot, we can observe that _id column showing data in object format.

    6. In database we have data stored in object format like “ObjectId(“59f2c3eba83a576fe07c735c”)”. I want to extract “59f2c3eba83a576fe07c735c” using SQL query but the above query is returning value in object format like “[B@2bc4de3d”

  6. To get the data in String format from the object, change the value of below property to false in Drill Options
  7. Open Drill UI and click on options

    8. Drill UI

  8. Search for the property “store.mongo.bson.record.reader” and change the value to “false”, click on Update.

    9. Store Mongo

  9. Now run the below query to fetch object ID from MongoDB as a String
    10. Query: select `p`.`_id`.`$oid` , `p`.`FirstName` from mongo.Sample.Person p

`_id`.`$oid` is a selector for the object Id to show the same in String format

    Output:

    Output

  10. Now in the above screenshot, we can see the data displayed in a String format
logo

Best Open Source Business Intelligence Software Helical Insight is Here

logo

A Business Intelligence Framework


logo

Best Open Source Business Intelligence Software Helical Insight is Here

logo

A Business Intelligence Framework


In case if you have any query please get us at support@helicaltech.com
Thank you
Ramu Vudugula
BI Developer
Helical IT Solutions Pvt Ltd

0 0 votes
Article Rating
Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments