We have been working on a N1QL. need help in changing it so that we can achieve the requirement

SQL++
,
28 
SELECT t3.id, t3.description, t3.dcrFlag, t3.lastModified, t3.name,
       (SELECT p1.*, p.data.displayName AS productName, p.uuid
        FROM api_external AS p USE KEYS t3.products[*].product
        LET p1 = FIRST {v.env, v.status} FOR v IN t3.products WHEN META(p).id == v.product END) AS products
FROM (SELECT t2.id,
           ARRAY_AGG({"product":"product::"||t2.product, t2.env, t2.status}) AS products,
           MIN({t2.description, t2.dcrFlag, t2.lastModified, t2.name}).*
      FROM (SELECT t1.id,product,
                 MIN(t1.env) AS env,
                 MAX(status) AS status,
                 MIN({t1.description, t1.dcrFlag, t1.lastModified, t1.name}).*
            FROM (SELECT m.appName AS name, m.uuid AS id, m.description, m.env, m.fourthParty AS dcrFlag,
                         m.createdTs AS lastModified, m.productStatus , m.productId , m.status
                    FROM api_external AS pa
                    JOIN api_external AS i1 ON pa.uuid = i1.data.partnerAppId AND i1.type = "integration"
                    WHERE pa.type = "partnerApp" AND pa.data.companyId = "70a149da27cc425da86cba890bf5b143"
                    GROUP BY pa.uuid, i1.data.productStatus
                    LETTING m = MAX([i1.createdTs, {pa.uuid, pa.data.appName, pa.data.description, pa.data.fourthParty,
                                                    i1.data.env, i1.data.productStatus , i1.createdTs,
                                                    i1.data.productId , i1.data.status }])[1]
                 ) AS t1
            LEFT UNNEST OBJECT_PAIRS(t1.productStatus) AS ps
            LET product = IFMISSINGORNULL(t1.productId, ps.name),
                status = IFMISSINGORNULL(t1.status, ps.val)
            GROUP BY t1.id, product) AS t2
      GROUP BY t2.id) AS t3;
29

Awesome !! thanks
I got it working too

SELECT t3.id, t3.description, t3.dcrFlag, t3.lastModified, t3.name,
       (SELECT p1.*, p.data.displayName AS productName, p.uuid
        FROM api_external AS p USE KEYS t3.products[*].product
        LET p1 = FIRST {v.env, v.status} FOR v IN t3.products WHEN META(p).id == v.product END) AS products
FROM (SELECT t2.id,
           ARRAY_AGG({"product":"product::"||t2.product, t2.env, t2.status}) AS products,
           MIN({t2.description, t2.dcrFlag, t2.lastModified, t2.name}).*
      FROM (SELECT t1.id,
                 case when t1.productId is not Null then t1.productId else ps.name end  AS product,
                 MIN(t1.env) AS env,
                 case when t1.productId is not Null then max(t1.status) else  MAX(ps.val) end AS status,
                 MIN({t1.description, t1.dcrFlag, t1.lastModified, t1.name}).*
            FROM (SELECT m.appName AS name, m.uuid AS id, m.description, m.env, m.fourthParty AS dcrFlag,
                         m.createdTs AS lastModified, m.productStatus,m.productId,m.status
                    FROM api_external AS pa
                    JOIN api_external AS i1 ON pa.uuid = i1.data.partnerAppId AND i1.type = "integration"
                    WHERE pa.type = "partnerApp" AND pa.data.companyId = "70a149da27cc425da86cba890bf5b143" AND i1.data.env != 'QA'
                    GROUP BY pa.uuid, i1.data.productStatus ,i1.data.productId,i1.data.status
                    LETTING m = MAX([i1.createdTs, {pa.uuid, pa.data.appName, pa.data.description, pa.data.fourthParty,
                                                    i1.data.env, i1.data.productStatus ,i1.data.productId,i1.data.status, i1.createdTs }])[1]
                 ) AS t1
            UNNEST OBJECT_PAIRS(t1.productStatus) AS ps
            GROUP BY t1.id, ps.name , t1.productId ) AS t2
      GROUP BY t2.id) AS t3