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.

Q & A Collection

https://stackoverflow.com/questions/47752325/creating-group-and-analyzing-data-from-array-list-in-birt