Find top 2 in an array

SQL++
,
1

I have a JSON which contains an array of payments done to different employees.
We need to write a query to find the highest 2 payments(and its details) done to each employee.
What would be an appropriate n1ql query ?

"payments" : 
        [
                {
			"name": "name1",
			"department": 30000,
			"id": "id1",
			"payment": 10,
		},
		{
			"name": "name2",
			"department": 30001,
			"id": "id2",
			"payment": 30,
		},
		{
			"name": "name1",
			"department": 30000,
			"id": "id1",
			"payment": 25,
		},
		{
			"name": "name1",
			"department": 30000,
			"id": "id1",
			"payment": 45,
		}
	]

expected response.

	[
		[{
				"name": "name1",
				"department": 30000,
				"id": "id1",
				"payment": 45,
			},
			{
				"name": "name1",
				"department": 30000,
				"id": "id1",
				"payment": 25,
			}
		],
		[{
			"name": "name2",
			"department": 30001,
			"id": "id2",
			"payment": 30,
		}]
	]
2 
INSERT INTO default VALUES("kk01",{"payments" : [{ "name": "name1", "department": 30000, "id": "id1", "payment": 10 }, { "name": "name2", "department": 30001, "id": "id2", "payment": 30 }, { "name": "name1", "department": 30000, "id": "id1", "payment": 25 }, { "name": "name1", "department": 30000, "id": "id1", "payment": 45 } ]});

SELECT  (SELECT p.* FROM d.payments AS p ORDER BY p.payment DESC LIMIT 2)  AS payments FROM default AS d ;

Checkout Nested Path Expressions in Subqueries

https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/subqueries.html

3

Thank you, but that does not give top 3 on .a per employee basis, right? It just gives the top 3 in the document.

4

If you need per employee basis across the documents, Unnest array, group by name and then do sub query expressions.

SELECT  d1.name, (SELECT p.* FROM  d1.np AS p ORDER BY p.payment DESC LIMIT 2)  AS payments  
FROM (SELECT  du.name, ARRAY_AGG(du) AS np
        FROM default AS d  UNNEST d.payments AS du
        GROUP BY du.name) AS d1;

Same query as above but the expected output you want (removes extra layers of objects)

SELECT  RAW (SELECT RAW p FROM  d1 AS p ORDER BY p.payment DESC LIMIT 2)
FROM ( SELECT  RAW ARRAY_AGG(du)
                 FROM default AS d  
                 UNNEST d.payments AS du
                GROUP BY du.name) AS d1;