Extract data from Mongo DB using R

[prateekraina]

Hi,

I have connected my Power BI desktop with Mongo DB using R already.

I am able to see the data inside my collection.

Problem Statement

The data stored in Mongo has nested JSON array which is not readable to Power BI Directly. I have tried writing different type of query but i am not able to access the child elements of the data.

Sample Data

{

	"imageName": "5a6188cceaf68a74e499de66_humanDetection_1516342489874.jpg",
	"createdAt": "1516342487874",
	"resultCount": "1",
	"camId": "5a6188cceaf68a74e499de66",
	"result": [{
		"id": "0",
		"bboxes": {
			"y2": "736",
			"y1": "257",
			"x2": "961",
			"x1": "751"
		},
		"areaOfInterestId": "5a618ccc926866aa7464529a"
	}]
}

Query Tried:

= R.Execute("library(mongolite)
  m<-mongo(collection="""&Text.From(Collection)&""",db = """&Text.From(Database)&""",url=paste(""mongodb://"","""&Text.From(Username)&""","":"","""&Text.From(Password)&""",""@"","""&Text.From(Server)&""","":"","""&Text.From(Port)&""",""/"","""&Text.From(Database)&""",sep=''), verbose = FALSE)#(lf)Query<-m$find(query = ""{}"", fields = ""{\""_id\"":1,\""resultCount\"":1,\""resukt.bboxes.y2\"":1}"", sort = ""{}"", skip = 0, limit = 0, handler = NULL, pagesize = 1000)")

Kindly  help in accessing the elements inside bboxes in Result array.

Prateek Raina

@ImkeF: Need your help on this.

[gilbertendaya]

@prateekraina @ImkeF how are you able to connect to MongoDB using R? please help as I need to use the same set-up instead of ODBC. Thank you.

[Anonymous]

Hi @prateekraina,

  Did you get any solution? I am also facing the same issue.

Thanks,

Sud

[prateekraina]

Hi @Anonymous ,

No, unfortunately I was not able to get solution for this.

Regards,

Prateek Raina

[WolfBiber]

Hey,

try something like

let
    RSC = R.Execute("library(mongolite)  m<-mongo(collection="""&Text.From(Collection)&""",db = """&Text.From(Database)&""",url=paste(""mongodb://"","""&Text.From(Username)&""","":"","""&Text.From(Password)&""",""@"","""&Text.From(Server)&""","":"","""&Text.From(Port)&""",""/"","""&Text.From(Database)&""",sep=''), verbose = FALSE)#(lf)Query<-m$find(query = ""{}"", fields = ""{\""_id\"":1,\""resultCount\"":1,\""resukt.bboxes.y2\"":1}"", sort = ""{}"", skip = 0, limit = 0, handler = NULL, pagesize = 1000)") ,
   Source = Json.Document(RSC)
in
    Source

in Query Editor (View-->Advanced Editor)

[prateekraina]

@WolfBiber

Thanks for responding but this is not working. I tried below as you mentioned:

let
    RSC = R.Execute("library(mongolite)
  m<-mongo(collection="""&Text.From(Collection)&""",db = """&Text.From(Database)&""",url=paste(""mongodb://"","""&Text.From(Username)&""","":"","""&Text.From(Password)&""",""@"","""&Text.From(Server)&""","":"","""&Text.From(Port)&""",""/"","""&Text.From(Database)&""",sep=''), verbose = FALSE)#(lf)Query<-m$find(query = ""{}"", fields = ""{\""_id\"":1,\""resultCount\"":1,\""result.bboxes.y2\"":1}"", sort = ""{}"", skip = 0, limit = 0, handler = NULL, pagesize = 1000)"),
    Source = Json.Document(RSC)
in
    Source

 Step 1 is successful.



However, step 2 is throwing below error:
Expression.Error: The parameter is expected to be of type Text.Type or Binary.Type



Prateek Raina

[WolfBiber]

Hi,

you have to use your result column (with the JSON data) in 

 Source = Json.Document(RSC.result)

But you have to look for the right syntax, cause I currently have no test data.

[prateekraina]

Hi @WolfBiber,

Is it not possible for you to look at the Step 1 snapshot and figure out the syntax based on the columns which are returned?

Prateek Raina

[ImkeF]

Hi @prateekraina,

so you want to extract the JSON-record in R an return the content of the sub-record bboxes as a table to Power BI?

This script works for me (with a link to the local json:

library(jsonlite)
json <- jsonlite::fromJSON("C:..Filepath/JsonMongoPrateek.json")
out <- json$result$bboxes

You can navigate with "$" through the nodes.

[prateekraina]

Hi @ImkeF,

It's great to hear from you. Actually, i need to use Mongolite as i am fetching the data from Mongo DB.

I would request you to go through the problem statement once again.

Prateek Raina

[ImkeF]

All I can recommend is to extract the full JSON from Mongo to R via the R-script in the query editor.

Once you have the JSON in R, you can use parts of my script to extract the inner parts there.

I have no clue how to do the extraction on MongoDB-level.

[prateekraina]

Hi @ImkeF,

The main issue is how to extract the full JSON from Mongo to R.
Using Mongolite i have not seen anywhere on internet of someone had tried to access a nested json array.

Can you please tag any expert who might have answer to this?

Prateek Raina

[ImkeF]

Sorry, but I don't know who is using this db.

I'd suggest to post your question on stackoverflow in the R-section (or Mongo).

[prateekraina]

Ok @ImkeF. Thanks for your time.

[Anonymous]

Hi@prateekraina.

Facing the same issue did you get any solution for this and also can you explain how you connected to MongoDB using R in power bi