How to write the following N1ql Query

SQL++
,
2 
CREATE INDEX ix1 ON default (productName) WHERE META().id LIKE "product::%";
CREATE INDEX ix2 ON default (partnerId) ;

SELECT d1.partnerId,
       ARRAY_AGG( {d1.productName
                   "env": CASE WHEN "PRODUCTION" IN d1.envs TEHN "PRODUCTION" ELSE "SANDBOX" END,
                    d1.Status
                  }) AS products
FROM ( WITH productObj AS ( OBJECT SUBSTR(v.id,9):v.productName
                            FOR v IN (SELECT META(p).id, p.productName
                                      FROM default AS p
                                      WHERE META(p).id LIKE "product::%" AND p.productName IS NOT NULL)
                            END
                          )
       SELECT d.partnerId,
              productName,
              ARRAY_AGG( DISTINCT op.val) AS Status
              ARRAY_AGG(DISTINCT d.env) AS envs
       FROM default AS d
       UNNEST OBJECT_PAIRS(d.productList) op
       LET productName = productObj.[op.name]
       WHERE d.partnerId IS NOT NULL
       GROUP BY d.partnerId, productName) AS d1
GROUP BY d1.partnerId;