N1Ql Query for group by and latest when compared with date ordered by desc

Hi,

I am trying to work out on creating a n1ql query which has to fetch the latest document in the list when compared to a given date ordered by desc and group by two fields.

Ex: Documents will be in below fashion
{
_Id = 1
"Key1 " : “7777”,
“Key2” : “abcdef”
“DateTime”: “2015-03-27T00:00:00Z”
“Date 2”: “2020-03-27T00:00:00Z”
“Other Field 1”: “Other Field 11”
“Other Field 2”: “Other Field 21”

}
{
_Id = 2
"Key1 " : “7777”,
“Key2” : “abcdef”
“DateTime”: “2016-03-27T00:00:00Z”
“Date 2”: “2020-03-28T00:00:00Z”
“Other Field 1”: “Other Field 12”
“Other Field 2”: “Other Field 22”

}
{
_Id = 3
"Key1 " : “7777”,
“Key2” : “abcdef”
“DateTime”: “2017-03-27T00:00:00Z”
“Date 2”: “2020-03-29T00:00:00Z”
“Other Field 1”: “Other Field 13”
“Other Field 2”: “Other Field 23”

}

{
_Id = 4
"Key1 " : “7777”,
“Key2” : “ghijkl”
“DateTime”: “2015-03-27T00:00:00Z”
“Date 2”: “2020-03-27T00:00:00Z”
“Other Field 1”: “Other Field 14”
“Other Field 2”: “Other Field 24”

}
{
_Id = 5
"Key1 " : “7777”,
“Key2” : “ghijkl”
“DateTime”: “2016-03-27T00:00:00Z”
“Date 2”: “2020-03-27T00:00:00Z”
“Other Field 1”: “Other Field 15”
“Other Field 2”: “Other Field 25”

}

Similarly documents with Key1 as “8888” as above.

{
_Id = 6
"Key1 " : “8888”,
“Key2” : “abcdef”
“DateTime”: “2015-03-27T00:00:00Z”
“Date 2”: “2020-03-27T00:00:00Z”
“Other Field 1”: “Other Field 11”
“Other Field 2”: “Other Field 21”

}
{
_Id = 7
"Key1 " : “8888”,
“Key2” : “abcdef”
“DateTime”: “2016-03-27T00:00:00Z”
“Date 2”: “2020-03-28T00:00:00Z”
“Other Field 1”: “Other Field 12”
“Other Field 2”: “Other Field 22”

}
{
_Id = 8
"Key1 " : “8888”,
“Key2” : “abcdef”
“DateTime”: “2017-03-27T00:00:00Z”
“Date 2”: “2020-03-29T00:00:00Z”
“Other Field 1”: “Other Field 13”
“Other Field 2”: “Other Field 23”

}

{
_Id = 9
"Key1 " : “8888”,
“Key2” : “ghijkl”
“DateTime”: “2015-03-27T00:00:00Z”
“Date 2”: “2020-03-27T00:00:00Z”
“Other Field 1”: “Other Field 14”
“Other Field 2”: “Other Field 24”

}
{
_Id = 10
"Key1 " : “8888”,
“Key2” : “ghijkl”
“DateTime”: “2016-03-27T00:00:00Z”
“Date 2”: “2020-03-27T00:00:00Z”
“Other Field 1”: “Other Field 15”
“Other Field 2”: “Other Field 25”

}

When queried with list of Key1 , list of Key2, and on particular date, it should fetch the top one from the list when ordered by desc for each Key1 and Key2 combination. The resultant should just provide me documents with _id just 2 and 7.

Here i need to select just Key1, Key2, Date2, OtherField1 and not every thing else from the documents. I have come up something like this,

select p.jp.Key1, p.jp.Key2, p.jp.Date2, p.jp.OtherField1 from (
SELECT max ([a.DateTime, a]) [1] as jp
from bucket as a
join bucket as b on KEYS META(a).id
WHERE a.Key1 in [“7777”,“8888”]
AND a.Key2 in [“abcdef”,“ghijkl”]
and a.DateTime <= 2016-03-28
group by a.Key1,a.Key2) p;

Is there any other best way to do achieve this? Is the query correct in the first place?

Tried something like below, but it doesn’t seem to work.

SELECT a.key1, a.key2, cmbyid[0].Date2 as Date2, cmbyid[0].OtherField1 as OtherField1,
from bucket as a
join bucket as b on KEYS META(a).id
WHERE a.Key1 in [“7777”,“8888”]
AND a.Key2 in [“abcdef”,“ghijkl”]
and a.DateTime <= 2016-03-28
group by a.key1, a.key2
LETTING cmbyid = ARRAY_AGG(a);

Thanks.

Please let me know if this can be achieved using spring JPA ?

CREATE INDEX ix1 ON bucket(Key1, DateTime, Key2, Date2, OtherField1);
SELECT a.Key1, a.Key2, MAX([a.DateTime, {a.Date2, a.OtherField1}])[1].*
FROM bucket AS a
WHERE a.Key1 IN ["7777","8888"]
AND a.DateTime <= 2016-03-28
GROUP by a.Key1, a.Key2;
1 Like

Thank you for the quick reply @vsr1

Hi @vsr1,
May be a silly question.
Can we have a map of values included in query such that the key of the given map should match against the key of query and values from map should get included in the result?
Ex: In general, we can use a static constant in select query as such

SELECT p._class,p.Key1, p.Key2, p.DateTime, p.Date2, “test” from bucket p where p._class=“com.xyz.abc” and p.key1 in [“7777”,“8888”]
Result will be like,
[
{
“$1”: “test”,
“_class”: “com.xyz.abc”,
"Key1 " : “7777”,
“Key2” : “ghijkl”,
“DateTime”: “2016-03-27T00:00:00Z”,
“Date 2”: “2020-03-27T00:00:00Z”
},
{
“$1”: “test”,
“_class”: “com.xyz.abc”,
“Key1 " : “8888”,
“Key2” : “ghijkl”
“DateTime”: “2016-03-27T00:00:00Z”,
“Date 2”: “2020-03-27T00:00:00Z”
}
]

I am just looking for something like,
SELECT p._class,p.Key1, p.Key2, p.DateTime, p.Date2,[“7777”:“A1”, "8888:“B2”] from bucket p where p._class=“com.xyz.abc” and p.key1 in [“7777”,“8888”] GROUP by p.Key1, p.Key2 …;

While retrieving the results, the values from the given input map ["7777":"A1", "8888:"B2"]when matched against the p.key1 values should get appended in the result documents. And variable should be specified as something like “Key3”

[
{
“Key1”: “A1”,
“_class”: “com.xyz.abc”,
"Key1 " : “7777”,
“Key2” : “ghijkl”,
“DateTime”: “2016-03-27T00:00:00Z”,
“Date 2”: “2020-03-27T00:00:00Z”
},
{
“Key3”: “B2”,
“_class”: “com.xyz.abc”,
“Key1 " : “8888”,
“Key2” : “ghijkl”
“DateTime”: “2016-03-27T00:00:00Z”,
“Date 2”: “2020-03-27T00:00:00Z”
}
]

Thank you.

SELECT p.Key1, 
       p.Key2,
       {"7777":"A1", "8888":"B2"}.[p.Key1] AS key3
FROM `bucket`  AS p 
WHERE  p._class="com.xyz.abc" AND  p.Key1 IN ["7777","8888"] 
GROUP BY p.Key1, p.Key2;


{"7777":"A1", "8888":"B2"} can be constant, named parameters or expression (in case of expression GROUP BY restrictions apply). 
p.key2 Must evaluate to string.

Thanks a lot @vsr1 !!