N1QL query to fetch the documents based on latest date (Descending order)

SQL++
1

I wanted to fetch the data based on the ‘respondedAt’ field using order by query but i get the above response. Ideally i should get -
i ran a query -

SELECT META(response).id, response.assessmentId, response.respondedAt,response.respondedBy, assessment.assessmentId
FROM secat-dev AS response
JOIN secat-dev AS assessment ON KEYS response.assessmentId
WHERE response.objectType = ‘response’ and response.inflight = false
order by response.respondedAt

my response is -
[
{“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“id”: “RESP-1548de3c-8dbf-4123-b0a3-4f4670ecafd4”,
“respondedAt”: “22-10-2017 01:25:36+0000”,
“respondedBy”: “RoseGold”
},
{“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“id”: “RESP-1443779f-b09c-4f3a-b9f4-ceaf5a71eb73”,
“respondedAt”: “26-11-2017 00:44:26+0000”,
“respondedBy”: “Smarthelp”
},
{“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“id”: “RESP-11441d1f-c150-48a9-a585-5f57a9271ebb”,
“respondedAt”: “24-12-2017 01:12:55+0000”,
“respondedBy”: “Pirates”
},
{“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“id”: “RESP-15674cc6-00cf-4ec7-9ef1-439527fba3e4”,
“respondedAt”: “20-01-2018 07:16:43+0000”,
“respondedBy”: “AnimalGiftingMIS”
},
{“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“id”: “RESP-180f998c-9614-4490-9860-df3c206d0ee3”,
“respondedAt”: “31-01-2018 05:50:10+0000”,
“respondedBy”: “Gravity”
}
]

Ideally i should get -

[
{
“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“id”: “RESP-180f998c-9614-4490-9860-df3c206d0ee3”,
“respondedAt”: “31-01-2018 05:50:10+0000”,
“respondedBy”: “Gravity”
},
{
“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“id”: “RESP-15674cc6-00cf-4ec7-9ef1-439527fba3e4”,
“respondedAt”: “20-01-2018 07:16:43+0000”,
“respondedBy”: “AnimalGiftingMIS”
},
{
“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“id”: “RESP-11441d1f-c150-48a9-a585-5f57a9271ebb”,
“respondedAt”: “24-12-2017 01:12:55+0000”,
“respondedBy”: “Pirates”
},
{
“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“id”: “RESP-1443779f-b09c-4f3a-b9f4-ceaf5a71eb73”,
“respondedAt”: “26-11-2017 00:44:26+0000”,
“respondedBy”: “Smarthelp”
},
{
“assessmentId”: “ASMT-e2ce728d-ab36-4216-81a6-193f15278aaa”,
“id”: “RESP-1548de3c-8dbf-4123-b0a3-4f4670ecafd4”,
“respondedAt”: “22-10-2017 01:25:36+0000”,
“respondedBy”: “RoseGold”
}
]

2

Your “respondedAt”: “22-10-2017 01:25:36+0000” not following ISO-8601 standard as described here https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/datefun.html
Once you change that to ISO-8601 standard it is string comparable and you can do DESC as follows.

SELECT META(response).id, response.assessmentId, response.respondedAt,
response.respondedBy, assessment.assessmentId
FROM secat-dev AS response
JOIN secat-dev AS assessment ON KEYS response.assessmentId
WHERE response.objectType = ‘response’ and response.inflight = false
ORDER BY  response.respondedAt DESC;
3

@vsr1 There are many records which have respondedAt field in nonISO 8601 date format. Is there a method in date functions using which we can convert “22-10-2017 01:25:36+0000” to “2017-10-22 01:25:36+0000” ?

4

There is no straight forward function but you can use SUBSTR and concat like below
select SUBSTR(date,6,4)||SUBSTR(date,2,4)||SUBSTR(date,0,2)||"T"||SUBSTR(date,11,11)||":"||SUBSTR(date,22,2) LET date="22-10-2017 01:25:36+0000" ;

SELECT META(response).id, response.assessmentId, response.respondedAt,
response.respondedBy, assessment.assessmentId
FROM secat-dev AS response
JOIN secat-dev AS assessment ON KEYS response.assessmentId
WHERE response.objectType = 'response' and response.inflight = false
ORDER BY  SUBSTR(response.respondedAt,6,4)||SUBSTR(response.respondedAt,2,4)||SUBSTR(response.respondedAt,0,2)||"T"||SUBSTR(response.respondedAt,11,11)||":"||SUBSTR(response.respondedAt,22,2) DESC;