{
"id" : "partnerAppID"
"products" :[ {
"productName" : "productName1",
"status" : "Requested"
"env" : "PRODUCTION" -- since product1 is in both sandbox and production we need to choose production and its corrosponding status
},
{
"productName" : "productName2",
"status" : "Approved"
"env" : "PRODUCTION" -- since product2 is in both sandbox and production we need to choose production and its corrosponding status
},
{
"productName" : "productName3",
"status" : "Approved"
"env" : "SANDBOX" -- since product3 is in only sandbox choose sandbox and its corresponding status
}
}
this is for an online app. what is the best way to get to this
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;
minor changes to the documents. there is a type field that tells what type of document it is and there are other documents and types in the bucket . Updated documents -