Noobie question regarding query

SQL++
1

Im new to couchbase/n1ql, so sorry if this is a stupid question:

SELECT * FROM gs_staging UNNEST gs_staging.tracks AS track WHERE gs_staging.cbType="course" AND gs_staging.status="live" AND ANY val IN track.schedule  SATISFIES val > 1542035900782 END ORDER BY gs_staging.created DESC LIMIT 4

This query returns the same document twice, not quite sure why. Also, the return format is

["gs_staging":{},"gs_staging":{}]

Is there a way to get rid of the keyspace wrapper?

Thanks and cheers,

Armin

2

You are doing UNNEST which repeats original document the array size and each time adds different array element.

If you are only interested if the given condition present in ARRAY use the nested ANY clause like below.

SELECT gs.*
FROM gs_staging AS gs 
WHERE gs.cbType="course" AND gs.status="live" AND 
           ANY track IN gs.tracks SATISFIES 
                   (ANY val IN track.schedule SATISFIES val > 1542035900782 END ) 
           END
ORDER BY gs.created  DESC 
LIMIT 4;
1 Like