Selecting Specific Array Element

Question

I am working with Couchbase v4.5 and the .NET SDK version 2.3.8. I am in the midst of migrating a view to a N1QL query. The Javascirpt available to the view provided me the flexibility of sorting array members of the document, and subsequently selecting just a single element from the array. So whereas in my view I had:

'Username': doc.Document.LogActions.sort(function(a,b){ return (a.Time > b.Time) })[doc.Document.LogActions.length - 1].Username

...I'm now struggling to accomplish the same via the .NET SDK in a N1QL query. The caveat is that I'm attempting to do this solely in LINQ. I realize that I can use the raw string queries, which offer full N1QL support, but the team lead wants this done via LINQ...unless there's absolutely no way to do it via LINQ.

I've tried the following:

Sorting - Not allowed to sort by a property on an object in an array

Username = doc.Document.LogActions
                       .OrderByDescending(logAction => logAction.Time)
                       .FirstOrDefault()
                       .Username,

Take Just Last - Last not supported

Username = transaction.Document.LogActions
                               .LastOrDefault()
                               .Username,

No Sorting - Still returns an array

Username = transaction.Document
                      .LogActions[transaction.Document.LogActions.Count - 1]
                      .Username

The only other thing I can think to do is just return the array back to .NET and do the filtering there. However, we have no baseline for how many items will be in the array on average. We'd rather avoid bringing back data we don't need.

Am I relegated to using a raw string query? Ideally I'm trying to find the most recent "log action" from the array, based on the Time property.

score 1 · Answer 1 · answered Nov 17 '17 at 23:41

I played around some more with this, and while it seems like something like this should work:

Username = transaction.Document.LogActions
                               .OrderByDescending(logAction => logAction.Time)
                               .Select(g => g.Username)
                               .ToArray()[0],

...alas, it does not. I receive an error of "N1Ql Array Subqueries Support One Ordering By The Array Elements Only". So I tried using a GroupBy--for giggles--and lo and behold I do get results back:

Username = transaction.Document.LogActions
                               .OrderByDescending(logAction => logAction.Time)
                               .GroupBy(logAction => new { logAction.Time, logAction.Username })
                               .Select(g => g.Key.Username)
                               .ToArray()[0],

This leaves me with confirming that I get the correct result back--since CB's ordering seems a bit wonky--but it's at least semi-working.

vsr · Answer 2 · 2017-11-24T18:02:35.713

I am not expert in LINQ. But the following is N1QL query.

MAX, MIN argument allows array. 0th element of array can be field needs aggregate and 1st element is what you want to carry. Using this technique you can project non aggregate field for MIN/MAX like below.

INSERT INTO default VALUES ("k01",{ "logActions":[ { "User":"u1", "Time":"2017-11-52T14:35:00Z" }, { "User":"u2", "Time":"2017-11-09T14:35:00Z" }, { "User":"u3", "Time":"2017-11-03T14:35:00Z" }, { "User":"u4", "Time":"2017-11-02T14:35:00Z" }, ] });
SELECT  MAX([u.Time,u.User])[1] AS User FROM default d USE KEYS "k01" UNNEST d.logActions AS a;

Also checkout N1QL : Find latest status from an array

In 5.0.0 using N1QL subquery expression can be achieved this https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/subqueries.html .

Selecting Specific Array Element

2 Answers2