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

SQL++
,
1

I have the following SQL that i have created till now -
That gives the following results .
We want to change the query in such a way that Final result can be achived.
This is an online app so please tell us if we need any indexes

Query we have achieved so far -

WITH ct3 AS (
		SELECT m[1].appName AS name
			,m[1].uuid AS id
			,m[1].description
			,m[1].env
			, m[1].productStatus
			,m[1].fourthParty AS dcrFlag
			,m[1].createdTs AS lastModified
		FROM api_external AS t4
		JOIN api_external AS t5 ON t4.uuid = t5.data.partnerAppId
		let product = OBJECT_PAIRS(t5.data.productStatus)[*].name
		WHERE t4.type = "partnerApp"
			AND t4.data.companyId = '0a5a3c4296a645cabb13a5ac10aff541'
			AND t5.type = "integration"
			AND t5.data.partnerAppId IS NOT NULL
		GROUP BY t4.uuid,t5.data.env,product LETTING m = MIN([-t5.createdTs, {t4.uuid, t4.data.appName, t4.data.description, t5.data.env, t5.data.productStatus , t4.data.fourthParty , t4.uuid,t5.createdTs }])
		)
SELECT ct3.name
	,ct3.id
	,ct3.description
	,ct3.dcrFlag
	,ct3.lastModified
	,(
		SELECT api_external.data.displayName AS productName
			,uuid AS productId,
			ct3.productStatus.[uuid] AS status,
			ct3.env
		FROM api_external USE KEYS(ARRAY "product::" || v FOR v IN OBJECT_NAMES(ct3.productStatus) END)
		) AS products
FROM ct3
where ct3.name ='test project to check multi integration' -- filter is to check for one UUID -this doesnt run in prod . this query will run for all uuids for a company

current results -


[

{
    "dcrFlag": false,
    "description": "",
    "id": "3f14d52d133943e8baa4fc9d56765ff5", 
    "lastModified": 1688080518990, -- this keeps getting updated to latest time stamp in the database 
    "name": "test project to check multi integration",
    "products": [
      {
        "env": "SANDBOX",
        "productId": "lmx@0e87d374ac4824bc2479fb008abcd",  
        "productName": "COD",
        "status": "REQUESTED"
      }
    ]
}
,{
    "dcrFlag": false,
    "description": "",
    "id": "3f14d52d133943e8baa4fc9d56765ff5", 
    "lastModified": 1688080516950, -- this keeps getting updated to latest time stamp in the database 
    "name": "test project to check multi integration",
    "products": [
      {
        "env": "PRODUCTION",
        "productId": "75e587d374ac4824bc2479fb008a004c",   -- requested status for production env
        "productName": "WORK product",
        "status": "REQUESTED"
      }
    ]
}
,
 {
    "dcrFlag": false,
    "description": "",
    "id": "3f14d52d133943e8baa4fc9d56765ff5",
    "lastModified": 1688080516420,
    "name": "test project to check multi integration",
    "products": [
      {
        "env": "SANDBOX",
        "productId": "75e587d374ac4824bc2479fb008a004c",   
        "productName": "WORK product",                     -- approved status  for sandbox sandbox
        "status": "APPROVED"
      },
      {
        "env": "SANDBOX",
        "productId": "aed8468f26424196aa69522b9b52160f",
        "productName": "Merchants",
        "status": "REQUESTED"
      }
    ]
  },
  {
    "dcrFlag": false,
    "description": "",
    "id": "3f14d52d133943e8baa4fc9d56765ff5",
    "lastModified": 1688080516420,
    "name": "test project to check multi integration",
    "products": [
      {
        "env": "SANDBOX",
        "productId": "75e587d374ac4824bc2479fb008a004c",
        "productName": "WORK product",
        "status": "REQUESTED"
      },
      {
        "env": "SANDBOX",
        "productId": "aed8468f26424196aa69522b9b52160f",
        "productName": "Merchants",  -- ADDED ANOTHER PRODUCT 
        "status": "REQUESTED"
      }
    ]
  },
  {
    "dcrFlag": false,
    "description": "",
    "id": "3f14d52d133943e8baa4fc9d56765ff5",
    "lastModified": 1687925437900,
    "name": "test project to check multi integration",
    "products": [
      {
        "env": "SANDBOX",
        "productId": "75e587d374ac4824bc2479fb008a004c",  -- ADDED 1 PRODUCT
        "productName": "WORK product",
        "status": "REQUESTED"
      }
    ]
  }
]

Final result must be -


[
	{
		"dcrFlag": false, -- will be same for all records of UUID can choose any
		"description": "", -- will be same for all records of UUID can choose any
		"id": "3f14d52d133943e8baa4fc9d56765ff5",   -- need 1 single record for each id ( uuid ) 
		"lastModified": 1688080518990, -- need to be latest modified time for that UUID 
		"name": "test project to check multi integration", -- will be same for all records of UUID can choose any
		"products": [
		  {
			"env": "PRODUCTION",
			"productId": "75e587d374ac4824bc2479fb008a004c",   -- for a UUID and product if we have both sandbox and production choose production and its status
			"productName": "WORK product",
			"status": "REQUESTED"                              -- for a environment if we have requested status and approved status in the above query result choose APPROVED
		  }, 
		  {
				"env": "SANDBOX",                               -- only sandbox entry is there for this product so keep it
				"productId": "aed8468f26424196aa69522b9b52160f",
				"productName": "Merchants",  
				"status": "REQUESTED"
		  },
		    {
				"env": "SANDBOX",								-- only sandbox entry is there for a product so keep it
				"productId": "lmx@0e87d374ac4824bc2479fb008abcd",  
				"productName": "COD",
				"status": "REQUESTED"
			}
		]
	}
]
2

Could you provide sample source documents for us to work with?

3

You need to work similar like How to write the following N1ql Query - #2 by vsr1
similar id aggregate all products and enc, status and use case.

5

We tried it and it dint work @vsr1 . That is why i am trying to give more details and ask in a different way . The documents I provided there were not actual. I will check if i can provide that.
Also i want to leverage existing indexes . this query gives good performance now so I need help only in rewriting -

SELECT ct3.name
	,ct3.id
	,ct3.description
	,ct3.dcrFlag
	,ct3.lastModified
	,(
		SELECT api_external.data.displayName AS productName
			,uuid AS productId,
			ct3.productStatus.[uuid] AS status,
			ct3.env
		FROM api_external USE KEYS(ARRAY "product::" || v FOR v IN OBJECT_NAMES(ct3.productStatus) END)
		) AS products
FROM ct3

part to achive this

[
	{
		"dcrFlag": false, -- will be same for all records of UUID can choose any
		"description": "", -- will be same for all records of UUID can choose any
		"id": "3f14d52d133943e8baa4fc9d56765ff5",   -- need 1 single record for each id ( uuid ) 
		"lastModified": 1688080518990, -- need to be latest modified time for that UUID 
		"name": "test project to check multi integration", -- will be same for all records of UUID can choose any
		"products": [
		  {
			"env": "PRODUCTION",
			"productId": "75e587d374ac4824bc2479fb008a004c",   -- for a UUID and product if we have both sandbox and production choose production and its status
			"productName": "WORK product",
			"status": "REQUESTED"                              -- for a environment if we have requested status and approved status in the above query result choose APPROVED
		  }, 
		  {
				"env": "SANDBOX",                               -- only sandbox entry is there for this product so keep it
				"productId": "aed8468f26424196aa69522b9b52160f",
				"productName": "Merchants",  
				"status": "REQUESTED"
		  },
		    {
				"env": "SANDBOX",								-- only sandbox entry is there for a product so keep it
				"productId": "lmx@0e87d374ac4824bc2479fb008abcd",  
				"productName": "COD",
				"status": "REQUESTED"
			}
		]
	}
]

can u help @vsr1

6

Any help will be greatly appreciated @vsr1 . Thanks a lot for your help on this

7 
CREATE INDEX ix2 ON api_external(data.companyId, uuid, data.appName, data.description, data.fourthParty ) WHERE type = "partnerApp";
CREATE INDEX ix3 ON api_external(data.partnerAppId) WHERE type = "integration";

SELECT t3.id, t3.description, t3.dcrFlag, t3.lastModified, t3.name,
       (SELECT p.env, p.status, p.data.displayName AS productName, p.uuid
        FROM api_external AS p USE KEYS t3.products[*].product) AS products) 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,
                 ps.name AS product
                 MIN(t1.env) AS env,
                 MAX(ps.val) 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
                    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 = '0a5a3c4296a645cabb13a5ac10aff541'
                    GROUP BY pa.uuid
                    LETTING m = MAX([i1.createdTs, {pa.uuid, pa.data.appName, pa.data.description, pa.data.fourthParty,
                                                    i1.data.env, i1.data.productStatus , i1.createdTs }])[1]
                 ) AS t1
            UNNEST OBJECT_PAIRS(t1.productStatus) AS ps
            GROUP BY t1.id, ps.name) AS t2
      GROUP BY t2.id) AS t3;
9

@vsr1 Thanks a lot for looking into this. Truely Appreciate it.

We checked this and tweaked this a little as per requirement.

SELECT t2.id,
           ARRAY_AGG({ 
                                 t2.env, 
                                 t2.status,
                                "product":(SELECT raw api_external.data.displayName FROM api_external USE KEYS "product::"||t2.product )[0]
                                 }) AS products,
           MIN({t2.description, t2.dcrFlag, t2.lastModified, t2.name}).*
      FROM (SELECT t1.id,
                 ps.name AS product,
                 MIN(t1.env) AS env,
                 MAX(ps.val) 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
                    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 = '<companyID>'
                    GROUP BY pa.uuid
                    LETTING m = MAX([i1.createdTs, {pa.uuid, pa.data.appName, pa.data.description, pa.data.fourthParty,
                                                    i1.data.env, i1.data.productStatus , i1.createdTs }])[1]
                 ) AS t1
                 UNNEST OBJECT_PAIRS(t1.productStatus) AS ps
            GROUP BY t1.id, ps.name
            ) AS t2
			where t2.name like '%testing sql%'
            GROUP BY t2.id

Its essentially the same thing you gave.
It is dropping off the products in sandbox state if any of the products is promoted to production.

for example -

[

{
    "dcrFlag": false,
    "description": "",
    "id": "3f14d52d133943e8baa4fc9d56765ff5", 
    "lastModified": 1688080518990, -- this keeps getting updated to latest time stamp in the database 
    "name": "test project to check multi integration",
    "products": [
      {
        "env": "SANDBOX",
        "productId": "lmx@0e87d374ac4824bc2479fb008abcd",  
        "productName": "COD",
        "status": "REQUESTED"
      }
    ]
}
,{
    "dcrFlag": false,
    "description": "",
    "id": "3f14d52d133943e8baa4fc9d56765ff5", 
    "lastModified": 1688080516950, -- this keeps getting updated to latest time stamp in the database 
    "name": "test project to check multi integration",
    "products": [
      {
        "env": "PRODUCTION",
        "productId": "75e587d374ac4824bc2479fb008a004c",   -- requested status for production env
        "productName": "WORK product",
        "status": "REQUESTED"
      }
    ]
}

]

if our previous SQL would give both these results

[
  {
    "dcrFlag": false,
    "description": "",
    "id": "3f14d52d133943e8baa4fc9d56765ff5",
    "lastModified": 1688080518990,
    "name": "testing my sql",
    "products": [
      {
        "env": "PRODUCTION",
        "product": "WORK product",
        "status": "REQUESTED"
      }, 
	   {
        "env": "SANDBOX",
        "productName": "COD",
        "status": "REQUESTED"
      }
    ]
  }
]

But right now its only giving this

[
  {
    "dcrFlag": false,
    "description": "",
    "id": "3f14d52d133943e8baa4fc9d56765ff5",
    "lastModified": 1688080518990,
    "name": "testing my sql",
    "products": [
      {
        "env": "PRODUCTION",
        "product": "WORK product",
        "status": "REQUESTED"
      }
    ]
  }
]

How do we fix this ?

10

Please find the latest set of rules for displaying-
Please follow the comments .
again thanks a lot for looking into this @vsr1 . Appreciate it

[
	{
		"dcrFlag": false, -- will be same for all records of UUID can choose any
		"description": "", -- will be same for all records of UUID can choose any
		"id": "3f14d52d133943e8baa4fc9d56765ff5",   -- need 1 single record for each id ( uuid ) 
		"lastModified": 1688080518990, -- need to be latest modified time for that UUID 
		"name": "test project to check multi integration", -- will be same for all records of UUID can choose any
		"products": [
		  {
			"env": "PRODUCTION",
			"productId": "75e587d374ac4824bc2479fb008a004c",   -- for a UUID and product if we have both sandbox and production choose production and its status
			"productName": "WORK product",
			"status": "REQUESTED"                              -- Please choose the latest status for the product and environment
		  }, 
		  {
				"env": "SANDBOX",                               -- only sandbox entry is there for this product so keep it
				"productId": "aed8468f26424196aa69522b9b52160f",
				"productName": "Merchants",  
				"status": "REQUESTED"
		  },
		    {
				"env": "SANDBOX",								-- only sandbox entry is there for a product so keep it
				"productId": "lmx@0e87d374ac4824bc2479fb008abcd",  
				"productName": "COD",
				"status": "REQUESTED"
			}
		]
	}
]
13

If it is complex query help, if you provide data so that it easy who ever answer question (cut and paste).
Like below:


UPSERT INTO default VALUES ( "p1", {"type":"partnerApp", "data":{"companyId": "0a5a3c4296a645cabb13a5ac10aff541", "appName": "test project to check multi integration", "description":"", "fourthParty":false }, "uuid":"3f14d52d133943e8baa4fc9d56765ff5"} );
UPSERT INTO default VALUES ( "i1", {"type":"integration", "data":{"partnerAppId": "3f14d52d133943e8baa4fc9d56765ff5", "env":"SANDBOX","productStatus":{"75e587d374ac4824bc2479fb008a004c" : "REQUESTED","aed8468f26424196aa69522b9b52160f" : "APPROVED"}}, "createdTs":1688080516420 } );
UPSERT INTO default VALUES ( "i2", {"type":"integration", "data":{"partnerAppId": "3f14d52d133943e8baa4fc9d56765ff5", "env":"SANDBOX","productStatus":{"75e587d374ac4824bc2479fb008a004c" : "REQUESTED"}}, "createdTs":1688080516420 } );
UPSERT INTO default VALUES ( "i3", {"type":"integration", "data":{"partnerAppId": "3f14d52d133943e8baa4fc9d56765ff5", "env":"SANDBOX","productStatus":{"lmx@0e87d374ac4824bc2479fb008abcd" : "REQUESTED"}}, "createdTs":1688080518990} );
UPSERT INTO default VALUES ( "i4", {"type":"integration", "data":{"partnerAppId": "3f14d52d133943e8baa4fc9d56765ff5", "env":"PRODUCTION","productStatus":{"75e587d374ac4824bc2479fb008a004c" : "REQUESTED"}}, "createdTs":1688080516950} );
UPSERT INTO default VALUES ("product::75e587d374ac4824bc2479fb008a004c", {"data":{"displayName": "WORK product"}, "uuid":"75e587d374ac4824bc2479fb008a004c"});
UPSERT INTO default VALUES ("product::lmx@0e87d374ac4824bc2479fb008abcd", {"data":{"displayName": "COD"}, "uuid":"lmx@0e87d374ac4824bc2479fb008abcd"});
UPSERT INTO default VALUES ("product::aed8468f26424196aa69522b9b52160f", {"data":{"displayName": "Merchants"}, "uuid":"aed8468f26424196aa69522b9b52160f"});
14 

SELECT t3.id, t3.description, t3.dcrFlag, t3.lastModified, t3.name,
       (SELECT p1.*, p.data.displayName AS productName, p.uuid
        FROM default 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,
                 ps.name AS product,
                 MIN(t1.env) AS env,
                 MAX(ps.val) 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
                    FROM default AS pa
                    JOIN default AS i1 ON pa.uuid = i1.data.partnerAppId AND i1.type = "integration"
                    WHERE pa.type = "partnerApp" AND pa.data.companyId = "0a5a3c4296a645cabb13a5ac10aff541"
                    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 }])[1]
                 ) AS t1
            UNNEST OBJECT_PAIRS(t1.productStatus) AS ps
            GROUP BY t1.id, ps.name) AS t2
      GROUP BY t2.id) AS t3;
15 
{
        "id": "3f14d52d133943e8baa4fc9d56765ff5",
        "description": "",
        "dcrFlag": false,
        "lastModified": 1688080516420,
        "name": "test project to check multi integration",
        "products": [
            {
                "env": "PRODUCTION",
                "productName": "WORK product",
                "status": "REQUESTED",
                "uuid": "75e587d374ac4824bc2479fb008a004c"
            },
            {
                "env": "SANDBOX",
                "productName": "Merchants",
                "status": "APPROVED",
                "uuid": "aed8468f26424196aa69522b9b52160f"
            },
            {
                "env": "SANDBOX",
                "productName": "COD",
                "status": "REQUESTED",
                "uuid": "lmx@0e87d374ac4824bc2479fb008abcd"
            }
        ]
    }
16

OR

SELECT t2.id,
       ARRAY_AGG({t2.env, t2.status, "uuid":t2.product,
                  "productName": (SELECT RAW p.data.displayName FROM default AS p USE KEYS "product::"||t2.product)[0]}) AS products,
       MIN({t2.description, t2.dcrFlag, t2.lastModified, t2.name}).*
FROM (SELECT t1.id,
             ps.name AS product,
             MIN(t1.env) AS env,
             MAX(ps.val) 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
             FROM default AS pa
             JOIN default AS i1 ON pa.uuid = i1.data.partnerAppId AND i1.type = "integration"
             WHERE pa.type = "partnerApp" AND pa.data.companyId = "0a5a3c4296a645cabb13a5ac10aff541"
             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 }])[1]
             ) AS t1
       UNNEST OBJECT_PAIRS(t1.productStatus) AS ps
       GROUP BY t1.id, ps.name) AS t2
GROUP BY t2.id;
19
  • Each matched id and product
    Looks “production”, “sandbox” order then duplicates choose latest timestamp, if duplicates choose “approved”, “requested”
SELECT MIN(t1.data).*,
        MAX(t1.createdTs) AS createdTs,
       ARRAY_AGG({t1.env, t1.status, "uuid":t1.product,
                  "productName": (SELECT RAW p.data.displayName FROM default AS p USE KEYS "product::"||t1.product)[0]}) AS products
FROM (SELECT m.*
      FROM default AS pa
      JOIN default AS i1 ON pa.uuid = i1.data.partnerAppId AND i1.type = "integration"
      UNNEST OBJECT_PAIRS(i1.data.productStatus) AS ps
      WHERE pa.type = "partnerApp" AND pa.data.companyId = "0a5a3c4296a645cabb13a5ac10aff541"
      GROUP BY pa.uuid, ps.name
      LETTING m = MIN([i1.data.env, -i1.createdTs, ps.val, {"data":{"id":pa.uuid, "name":pa.data.appName, pa.data.description,
                                                            "dcrFlag": pa.data.fourthParty}, i1.createdTs,
                                                            "product":ps.name, i1.data.env, "status": ps.val}])[3]
      ) AS t1
GROUP BY t1.data.id;
20

Woww !! Awesome @vsr1 .
You are simply the best .
I hope I can connect with you some how and take you for a lunch .
Thanks a lot for your time on this .

21

Hi @vsr1
for the above query

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,
                 ps.name AS product,
                 MIN(t1.env) AS env,
                 MAX(ps.val) 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
                    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 = $ID AND i1.data.env != 'QA'
                    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 }])[1]
                 ) AS t1
            UNNEST OBJECT_PAIRS(t1.productStatus) AS ps
            GROUP BY t1.id, ps.name) AS t2
      GROUP BY t2.id) AS t3

The base query is partnerApp joining with integration.

we are facing issues with some records as the way integration records are represented are different in some scenarios.

SCENARIO 1 - 
============
{
  "uuid": "0d47484803d940ddb2eabaa0fa15anskda",
  "type": "integration",
  "createdTs": "2023-03-24T17:57:59.227+00:00",
  "backupOf": null,
  "updatedTs": "2023-03-24T17:57:59.829+00:00",
  "deleted": false,
  "data": {
    "partnerAppId": "3cd91ad066644aecbdee5d876d276cc6",
    "productId": null,
    "productStatus": {
      "6dcd5199e5ab466eb3c1a7637a06axjdalsx": "REQUESTED",
      "13fb46f6bf044c92aa5c265e20ecdslamd03": "REQUESTED"
    },
    "env": "SANDBOX",
    "status": null,
    "
  }
}

SCENARIO 2 - 
===========

{
  "uuid": "9457484803d940ddb2eabaa0fahafkjalsn",
  "type": "integration",
  "createdTs": "2023-03-24T17:57:59.227+00:00",
  "backupOf": null,
  "updatedTs": "2023-03-24T17:57:59.829+00:00",
  "deleted": false,
  "data": {
    "partnerAppId": "dhALDAad066644aecbdee5d876d276cc6",
    "productId": "6dcd5199e5ab466eb3c1a7637a06axjdalsx",
    "productStatus": {
    },
    "env": "SANDBOX",
    "status": "REQUESTED",
    
  }
}

In these cases ( scenario 2 ) if productId is specifically available with a status value. only that should be considered, and we should disregard productStatus field to get the productId and status. How do we account for that requirement in the above N1QL you provided

22

Add AND i1.status IS NOT NULL;

23

@vsr1 what i mean is if productId and status are available in seperate fields those to fields must be considered instead
Consider the second level N1QL

SELECT t1.id,
                 ps.name AS product, -- if productId  is available take that only
                 MIN(t1.env) AS env,
                 MAX(ps.val) AS status, -- if status is available we have to consider that only
                 MIN({t1.description, t1.dcrFlag, t1.lastModified, t1.name}).*
25 
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,
                 ps.name AS product,
                 MIN(t1.env) AS env,
                 MAX(ps.val) 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
                    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 = $ID AND i1.data.env != 'QA'
                    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 }])[1]
                 ) AS t1
            UNNEST OBJECT_PAIRS(t1.productStatus) AS ps
             WHERE ps.name IS NOT NULL AND ps.val IS NOT NULL
            GROUP BY t1.id, ps.name) AS t2
      GROUP BY t2.id) AS t3
26

@vsr1
I had tried few things, but they failed due to syntax error. but it may help understand what i am trying.
check the letting part and second level select with case statements that is
As I mentioned its giving syntax error.

SELECT t1.id,
                 CASE WHEN t1.productId is not null then t1.productId else t1.ps.name END AS product,
                 MIN(t1.env) AS env,
                 CASE WHEN t1.productId is not null then t1.status else MAX(ps.val) END AS status,
                 MIN({t1.description, t1.dcrFlag, t1.lastModified, t1.name}).*

FUll Query -


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 t1.ps.name END AS product,
                 MIN(t1.env) AS env,
                 CASE WHEN t1.productId is not null 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"
                    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
            UNNEST OBJECT_PAIRS(t1.productStatus) AS ps
            GROUP BY t1.id, ps.name) AS t2
      GROUP BY t2.id) AS t3;

As i mentioned its giving syntax error

27

THEN keyword is missing,
cratedTs** – double star extra