Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@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.
Hi @Anonymous ,
No, unfortunately I was not able to get solution for this.
Regards,
Prateek Raina
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)
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
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.
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
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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).
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |