Analytical Functions In MongoDB

[Parthapratim Mukherjee]

Hi, I am new to mongodb and I want to know whether there are any equivalent functions similar to Oracle SQL analytical functions like Dense_rank(),Count() Over (partition by ...), Lead(),Lag(),width_bucket() in mongo.

If not what is there any way to implement such functionalities in mongo queries.

-Thanks!

[Rhys Campbell]

You probably want to take a look at the aggregation framework although I think you'll need to fall back to MapReduce for some of the functions you mention.

[John De Goes]

The open source project Quasar will eventually get such SQL functions, and you won't have to use MapReduce either. Quasar compiles SQL to low-level, optimized Mongo queries, and the primary use case is analytics.

Regards,

John

[Parthapratim Mukherjee]

Thanks for the pointer. I have used map reduce to implement a function like Oracle SQL Row_Number().

However I got stuck to another problem. How can I do unpivot in mongo. 

For example,lets say I have a document of student details like 

{_id:1000,Student: "abc", Subject_Details:[{Name: "Mathematics", High:90, Low: 10, Avg: 50},{Name: "Science", High:85, Low: 5, Avg: 45}]}

From the above document I want to derive to 6 documents like

 

{_id:1000,Student: "abc", Subject_Name:"Mathematics" Type: "High" , Value:90}

{_id:1000,Student: "abc", Subject_Name:"Mathematics" Type: "Low" , Value:10}

{_id:1000,Student: "abc", Subject_Name:"Mathematics" Type: "Avg" , Value:50}

{_id:1000,Student: "abc", Subject_Name:"Science" Type: "High" , Value:85}

{_id:1000,Student: "abc", Subject_Name:"Science" Type: "Low" , Value:5}

{_id:1000,Student: "abc", Subject_Name:"Science" Type: "Avg" , Value:45}

  

I have seen $unwind but it only flattens a array, But how can I get two fields like  Type: "High" , Value:90 from a single field High:90