Select object containing value nearest to the specified value from an array

SQL++
,
1

Hi, I have a document type ‘Visitor’ which contains a collection ‘visits’ as
"visits": [
{
“endDate”: “2018-01-09T16:30:00”,
“departmentCode”: “HS”,
“startDate”: “2018-01-09T09:30:00”,
“visitId”: “19a1943c-160c-43bd-93f8-026f32e16ae1”,
“visitPurposeId”: “d238dd29-9af0-446a-af6b-5c6b9ac37078”
},
{
“endDate”: “2018-01-10T16:30:00”,
“departmentCode”: “HS”,
“startDate”: “2018-01-10T09:30:00”,
“visitId”: “61887c1f-4f87-40cf-b475-14b55a0f3751”,
“visitPurposeId”: “62157952-0926-499c-ae29-5476183bb34e”
},
{
“endDate”: “2018-01-15T16:30:00”,
“departmentCode”: “HS”,
“startDate”: “2018-01-15T09:30:00”,
“visitId”: “8850a7df-7791-4180-b001-f971497f1c81”,
“visitPurposeId”: “85748c1a-67b5-47bd-9b92-f9174e6ce924”
}
]

I want to select the object which contains ‘startDate’ nearest to the date specified.
For example, for above document , if ‘2018-01-11’ is passed, then the object containing ‘startDate’ = “2018-01-10” should be returned. and if ‘2018-01-13’ is passed, then the object containing ‘startDate’ = “2018-01-15” should be returned. In case of exact match, the one with matching value should be returned.

How can I implement this without making the query too complex?
The query should be indexable too.

2

My Solution:

SELECT FIRST v for v in visits When ABS(Date_diff_str(v.startDate , '2018-01-11T08:00:00', 'day')) = smallestDiff END as NearestVisit
From reporting
let smallestDiff = ARRAY_MIN(ARRAY ABS(Date_diff_str(v.startDate , '2018-01-11T08:00:00', 'day')) for v in visits when v.departmentCode = 'HS' and Date_format_str(v.startDate, '1111-11-11') Between '2018-01-09' and '2018-01-16' END)
Where type = 'Visitor' ;

Please let me know if any improvement is poaaible here

3

SELECT (SELECT RAW MIN([ABS(DATE_DIFF_STR(visit.startDate , '2018-01-11T08:00:00', 'day')),visit])[1] FROM d.visits AS visit WHERE visit.departmentCode = "HS") [0] AS NearestVisit FROM reporting AS d WHERE d.type = "Visitor";

4

Worked like a charm! Thanks a lot @vsr1 .

5

You can also use ABS(MILLIS(visit.startDate) -MILLIS(‘2018-01-11T08:00:00’))