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 whic...
Need Help
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.

    • prateekraina's avatar
      prateekraina
      Memorable Member
      8 years ago

      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's avatar
        ImkeF
        Community Champion
        8 years ago

        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.