RaqForum 14 No.
807 View  • 
     ...

Can MongoDB be queried with SQL?

 

The relational database data model is simple, and they are all single-level two-dimensional tables with clear rows and columns. It is relatively simple to use SQL. In MongoDB, there is a multi-level nested structure. The attribute fields appear arbitrarily, and it is not easy to describe which field information of which layer is selected. Information is not easy. Some third-party tools can also support the use of SQL on MongoDB, but they have very limited capabilities and cannot handle the nested structure involved in JSON storage. They are only a subset of the full capabilities of SQL. If you use Open-esProc, first simple query through MongoDB, and then combined with SPL syntax (SQL-like calculation), not only can complete all the functions of SQL, but also particularly good at processing multi-layer data.

For example, to sum the data in the embedded document, it is necessary to count the sum of income and output of each record. The data is as follows.

_id income output
1 ｛"cpu":1000, "mem":500,   "mouse":"100"｝ ｛"cpu":1000, "mem":600 ,"mouse":"120"｝
2 ｛"cpu":2000, "mem":1000,
  "mouse":"50","mainboard":500 ｝		 {"cpu":1500, "mem":300 ｝

Expect calculation results:

_id income output
1 1600 1720
2 3550 1800

Processing with SPL script is as follows:


 A B
1 =mongo_open("mongodb://127.0.0.1:27017/raqdb")
2 =mongo_shell(A1,"computer.find()").fetch()
3 =A2.new(_id:ID,income.array().sum():INCOME,output.array().sum():OUTPUT)
4 >A1.close()

SPL fully supports SQL-like functions and simplifies MongoDB query scripts. For example, count the number of records in each segment. The following is segmented by sales volume, and the data volume in each segment is calculated. The data is as follows:

_id NAME STATE SALES
1 Ashley New York 11000
2 Rachel Montana 9000
3 Emily New York 8800
4 Matthew Texas 8000
5 Alexis Illinois 14000

Segmentation method: 0-3000; 3000-5000; 5000-7500; 7500-10000; above 10000.

Expect calculation results:

Segment number
3 3
4 2

Grouped by conditions, MongoDB does not provide corresponding processing methods, which is cumbersome to implement, and it is very simple in SPL:


 A B
1 [3000,5000,7500,10000,15000]
2 =mongo_open("mongodb://127.0.0.1:27017/raqdb")
3 =mongo_shell(A2,"sales.find()").fetch()
4 =A3.groups(A1.pseg(int(~.SALES)):Segment;count(1):   number)
5 >A2.close()

Many complex query calculations in MongoDB can be implemented with simple SPL scripts. For more calculation examples, see SPL-driven MongoDB Hackers

After the SPL script calculates the MongoDB data, the results can also be easily used by java applications. SPL has a dedicated JDBC driver. SPL scripts are called through JDBC. For details, please refer to How to perform SQL-like queries on MongoDB in Java?

 
 •  807 View  •