Filtering what properties to include in new array construct

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?

Hi @chrisva
Have you tried filtering the Array with a statement like this, on the created array?

SELECT *
FROM carArray
WHERE ANY car IN cars SATISFIES
car > 0 END

Hi @martinesmann,

Yes, I tried, but it gives me:

[
  {
    "code": 3000,
    "msg": "Ambiguous reference to field cars."
  }
]

So, does that mean I can’t filter using the alias?

Hi @chrisva,
I will setup a test environment and do some testing.

1 Like

Hi,

You can use the ARRAY construct:

ARRAY x FOR x IN my_array WHEN my_condition(x) END