Forum Discussion

prateekraina's avatar
prateekraina
Memorable Member
8 years ago

Extract data from Mongo DB using R

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.

Need Help

14 Replies

  • WolfBiber's avatar
    WolfBiber
    Microsoft Employee
    8 years ago

    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's avatar
      prateekraina
      Memorable Member
      8 years ago

      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's avatar
        WolfBiber
        Microsoft Employee
        8 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi prateekraina,

     

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

     

    Thanks,

    Sud

    • prateekraina's avatar
      prateekraina
      Memorable Member
      7 years ago

      Hi Anonymous ,

       

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

       

      Regards,

      Prateek Raina