I have a requirement to select relation id ,details based on age in descending order. (array_reverse & array sort function can be used for a single array). Here in the below example " details" is an array within “Relations”
It would be very helpful if anyone guides on writing N1QL queries to sort based on age in desc and display relation id, fname
{
"tutorial": {
"type": "contact",
"parentname": "Ian",
"email": "ian@gmail.com",
"relations": [
{
"RelationId": "100",
"Details": [{
"fname": "Abama",
"age": 16,
"gender": "F"
},
{
"fname": "Sophia",
"age": 18,
"gender": "F"
}]
},
{
"RelationId": "101",
"Details": [{
"fname": "Alex",
"age": 17,
"gender": "M"
}]
}
]
}
}
I am able to sort details based on age within the details array for each relation Id but I want to sort details based on age across the relation Id and output like as shown below
RelationId Fname Age
100 Sophia 18
101 Alex 17
100 Abama 16
vsr1
February 2, 2021, 1:51pm
2
SELECT r.RelationId, d.age, d.fname
FROM tutorial AS t
UNNEST t.relations AS r
UNNEST r.Details AS d
WHERE t.type = "contact';
Thank you for the reply. Need to sort based on descending order of age adding order by d.age desc is not working.
vsr1
February 2, 2021, 2:26pm
4
I will work. If not working post the query and output
SELECT r.RelationId, d.age, d.fname
FROM tutorial AS t
UNNEST t.relations AS r
UNNEST r.Details AS d
WHERE t.type = "contact’
order by d.age desc
rest part of the query works completely fine as given by you .But not getting the sorted output based on age desc
vsr1
February 2, 2021, 2:35pm
6
SELECT r.RelationId, d.age, d.fname FROM [{ "type": "contact", "parentname": "Ian", "email": "ian@gmail.com", "relations": [ { "RelationId": "100", "Details": [{ "fname": "Abama", "age": 16, "gender": "F" }, { "fname": "Sophia", "age": 18, "gender": "F" }] }, { "RelationId": "101", "Details": [{ "fname": "Alex", "age": 17, "gender": "M" }] } ] } ] AS t UNNEST t.relations AS r UNNEST r.Details AS d WHERE t.type = "contact" ORDER BY d.age DESC;
{
"requestID": "c048aac0-580c-4c27-87a5-7e383b152ac2",
"signature": {
"RelationId": "json",
"age": "json",
"fname": "json"
},
"results": [
{
"RelationId": "100",
"age": 18,
"fname": "Sophia"
},
{
"RelationId": "101",
"age": 17,
"fname": "Alex"
},
{
"RelationId": "100",
"age": 16,
"fname": "Abama"
}
],
"status": "success",
"metrics": {
"elapsedTime": "5.23261ms",
"executionTime": "5.13198ms",
"resultCount": 3,
"resultSize": 240,
"serviceLoad": 2,
"sortCount": 3
}
}
Need to put the entire document in the from block? .I am trying to understand the query
vsr1
February 2, 2021, 4:12pm
8
No. This is show the query works and giving in the order. Your query one earlier is right one.