We want to have a some optimised covering indexes to make this N1QL run the fastest possible. in short we want all data for this Query to be in indexes
Below are the queries for which we need in memory indexes for. Please note both the N1QL just differ in the where clause and we need in memory indexes for same
SELECT t3.id,
t3.description,
t3.dcrFlag,
t3.lastModified,
t3.name,
(SELECT p1.*,
p.data.displayName AS productName,
p.uuid productId
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,
coalesce(m.updatedTs,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 = "ID"
AND pa.deleted = false
AND i1.deleted = false
AND i1.data.env !="QA"
AND (IS_MISSING(i1.backupOf)
OR IS_NULL (i1.backupOf)
OR i1.backupOf="")
GROUP BY pa.uuid, i1.data.productStatus LETTING m = MIN([i1.data.env, {pa.uuid, pa.data.appName, pa.data.description, pa.data.fourthParty, i1.data.env, i1.data.productStatus , i1.createdTs,i1.updatedTs, 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;
SELECT t3.id,
t3.description,
t3.dcrFlag,
t3.lastModified,
t3.name,
(SELECT p1.*,
p.data.displayName AS productName,
p.uuid productId
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,
coalesce(m.updatedTs,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 ("73fbc0a557b54732a991f4bb7dbda327" in pa.data.additionalAdmins OR "73fbc0a557b54732a991f4bb7dbda327" in pa.data.additionalDevelopers)
AND pa.deleted = false
AND i1.deleted = false
AND i1.data.env !="QA"
AND (IS_MISSING(i1.backupOf)
OR IS_NULL (i1.backupOf)
OR i1.backupOf="")
GROUP BY pa.uuid, i1.data.productStatus LETTING m = MIN([i1.data.env, {pa.uuid, pa.data.appName, pa.data.description, pa.data.fourthParty, i1.data.env, i1.data.productStatus , i1.createdTs,i1.updatedTs, 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;
