Select specific array elements

SQL++
1

Hello,

I’m unsure how to achieve what I want, my current query looks like this

SELECT META().id FROM hotels as h WHERE _class='Hotel' AND ANY facility IN h.facilities SATISFIES facility.facilityGroupCode = 91 END;

facilites is an array of objects that have facilityGroupCode and description

This query will give me the ids of the objects that have at least one facility with facilityGroupCode 91, but I want to to have the facilities that match the code 91 in the query result (instead of only the document id), so I can get everything I need in one query.

I would appreciate any tips, thanks a lot.

2 
SELECT META().id , ARRAY v FOR v IN h.facilities WHEN v.facilityGroupCode = 91 END AS facilities
FROM hotels as h 
WHERE _class='Hotel' AND ANY facility IN h.facilities SATISFIES facility.facilityGroupCode = 91 END;

OR

   SELECT META(h).id , facility.*
    FROM hotels as h 
    UNNEST  h.facilities  AS facility
    WHERE h._class='Hotel' AND facility.facilityGroupCode = 91 ;
1 Like