Group and Summarize Multilevel Mongo Data
Problem description & analysis
A MongoDB database contains the following collection devicestatus, whose data is as follows:
{
"_id" : "0001",
"className" : "store",
"deviceStatus" : [
{
"deviceName" : "CardReader",
"errorCode" : "97080301",
"status" : "Bad"
},
{
"deviceName" : "CashAcceptor",
"errorCode" : "97080302,97080303",
"status" : "Bad"
},
{
"deviceName" : "CashDispenser",
"errorCode" : "",
"status" : "Good"
}
]
}
We are trying to group and summarize the multilevel data. The expected result is as follows:
className |
deviceName |
errorCode |
status |
store |
CardReader |
97080301 |
Bad |
store |
CashAcceptor |
97080302,97080303 |
Bad |
store |
CashDispenser |
Good |
Solution
We write the following script p1.dfx in esProc:
A |
|
1 |
=mongo_open("mongodb://127.0.0.1:27017/raqsoft") |
2 |
=mongo_shell@x(A1,"devicestatus.find()").fetch() |
3 |
=A2.news(deviceStatus;className,~.deviceName,~.errorCode,~.status) |
Explanation:
A1 Connect to database raqsoft on mongodb server through IP: 127.0.0.1 and port 27017 and without the username and password.
A2 Query the database and return result. @x option enables auto-closing the database connection.
A3 Loop through each row of A2’s table sequence. Generate a row for each document under deviceStatus. Each new row consists of four columns. The first column is className, and the rest are deviceName, errorCosde and status. The ~ represents the current document under deviceStatus.
Read How to Call an SPL Script in BIRT to learn about the method of integrating the SPL script into BIRT.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL