UNNEST within an UNNEST

SQL++
1

I wanted to filter the results for an array within an array. My end result should have only the outlets which are ‘Active’.

Document:

[
  {
    "name": "Sample offerSet1",
    "offerSetId": 7,
    "offers": [
      {
        "offerId": 2,
		"status": "Active",
        "outlets": [
          {
            "outletId": 1,
            "status": "Active"
          },
          {
            "outletId": 2,
            "status": "Deleted"
          }
        ]
      }
    ],
    "status": "Active",
    "type": "OfferSet"
  }
]

N1QL:

SELECT object_remove(offerSet, 'offers').*, ARRAY_AGG(offers) AS offers
FROM bucket offerSet
UNNEST offerSet.offers AS offers
UNNEST offers.outlets AS outlets
WHERE offerSet.type='OfferSet'
AND offers.status = 'Active'
AND outlets.status = 'Active'
AND EXISTS (SELECT allOffers.offerId FROM bucket allOffers USE KEYS 'offer_' || to_STRING(offers.offerId) WHERE allOffers.status = 'Active')
GROUP BY offerSet;

In the above N1QL, i’m able to filter only the ‘Active’ offers. But i’m not able to filter the ‘Active’ outlets though i UNNEST the outlets array. Or may be i don’t know how to use an ARRAY_AGG within an ARRAY_AGG.
Can someone please help ?

2

SELECT object_remove(offerSet, ‘offers’).*, ARRAY_AGG(offer)
FROM bucket offerSet
UNNEST offerSet.offers AS offers
LET offer = ARRAY o FOR o in offers.outlets when o.status = ‘Active’ END
WHERE offerSet.type=‘OfferSet’ AND offers.status = 'Active’
GROUP BY offerSet;

4

Hi, it sounds like you need something like this.

UPDATE ...
SET a.actionStatus = 'PENDING' FOR a IN actions WHEN (ANY AND EVERY d IN a.dependencies SATISFIES ... END) END