Hi @vsr1,
SELECT MIN([ instance.expiryTime, instance])[1].*
FROM publish AS instance
JOIN publish AS fragment ON (instance.fragmentId = fragment.[‘path’])
WHERE fragment.externalId IN [“123”,“456”]
AND (instance.effectiveTime <= 1608548624000
AND 1608548624000 <= instance.expiryTime)
GROUP BY instance.fragmentId
I have bucket publish which contains document termed as a fragment, there can only be one fragment document with an external id, as it is a unique field. There can be multiple instance document separated by time logic of effective and expiry.now each instance will have a field fragmentID, there can be multiple instances with same fragmentid. this fragmentid is basically the path key in the fragments.
Now I have to pick one instances of each external id which I am passing, based on sorting of expiry time.
I have written the above query for this, and it is giving me a single document for each external id, but I also need few fragment keys from the document, which I am unable to get, can you help me in this.
i want to read fragment.externalId I have modified the query like this but it is not generating any output.
SELECT MIN([ instance.expiryTime, instance,fragment.externalId])[1].*
FROM publish AS instance
JOIN publish AS fragment ON (instance.fragmentId = fragment.[‘path’])
WHERE fragment.externalId IN [“123”,“456”]
AND (instance.effectiveTime <= 1608548624000
AND 1608548624000 <= instance.expiryTime)
GROUP BY instance.fragmentId
Thank you