I am diving into N1QL and I try to create a new structure from the following JSON:
[{
"garageId": 1,
"car1Id": 1233,
"car2Id": 1211,
"car3Id": 1339,
"car4Id": 0,
"dataType": "garage-entity"
}]
The JSON above has a flat structure because we feed data from the original data store close to real time. We don’t want to transform the data during that process. So using N1QL I am able to put my car id’s into an array for easier consumption in our HTML client using the following query:
select t.garageId, TOARRAY([tsl.car1, tsl.car2, tsl.car3 tsl.car4) cars
FROM default t
WHERE t.dataType = 'garage-entity'
The result from that query is:
[{
"garageId": 1,
"cars": [
1233,
1211,
1339,
0
]
}]
This is exactly what I want, but I also want to filter away the element in the array that contains 0. What would be the best way to do that in the same operation?
The optimized result would then look like this:
[{
"garageId": 1,
"cars": [
1233,
1211,
1339
]
}]
Can this be done?