How do we write the following N1QL

SQL++
,
1

I have the below project records.

"projectkey1" : { 
 "name" : "project name",
"productDetails" :{
"productKey1":"requested",
"productkey2":"requested"
} 
"type" : "project"
}

Now each product is stored as below are
 "productKey1"(key) : {"description" : "product1" , "type" : "product"}

We have multiple project records but what products are associated with the project is shown in the above key as key value pairs with the actual product key as a key in the project record. Also there is no limit on how many product keys a project can have. Given these requirements how can i write a N1ql that will give the projectname and the product description in the same json

Some thing like

 [{ 
    "name" : "project name",
    "product list" : ["product description1", "product description2"]
    },
    { 
    "name" : "project name2",
    "product list" : ["product description3", "product description4"]
    }
]

PS : all the records are in the same bucket

2 
CREATE INDEX ix1 ON  default(name ) WHERE type = "project";
OR
CREATE INDEX ix2 ON default (name, OBJECT_NAMES(productDetails) ) WHERE type = "project";

SELECT p.name,
       (SELECT RAW pd.description
        FROM default AS pd USE KEYS OBJECT_NAMES(p.productDetails)
        WHERE pd.type = "product"
       ) AS productList
FROM default AS p
WHERE p.type = "project"
      AND p.name IS NOT NULL;

Use ix2 product keys limited say less than 50 otherwise index can ballon

4

I tried the above idea . But facing some issue . any pointers ? I realised i cant use keys as keys have a constant string concatenated to it. Instead the records have same product id values as UUID
Added this -
select raw api_external.data.displayName from api_external where api_external.type = ‘product’ and api_external.uuid in OBJECT_NAMES(t2.productStatus)

error -

“code”: 5370,
“msg”: “Unable to run subquery - cause: FROM in correlated subquery must have USE KEYS clause: FROM api_external.”,

WITH ct3 AS (select t1.uuid as uuid_proj ,
t1.appName as appName ,
t1.description as description,
t2.env as env,
(select raw api_external.data.displayName from api_external where api_external.type = ‘product’ and api_external.uuid in OBJECT_NAMES(t2.productStatus)) as productStatus
FROM ( select
api_external.uuid ,
api_external.data.appName ,
api_external.data.description
from api_external
where type = ‘partnerApp’
and data.companyId = ‘70a149da27cc425da86cba890bf5b143’ ) t1
JOIN ( select
api_external.data.env,
api_external.data.productStatus,
api_external.data.partnerAppId
from api_external
where type = ‘integration’ ) t2
ON t1.uuid = t2.partnerAppId),
ct6 AS ( select t4.uuid as uuid_agg , min(t5.env) as env
FROM (select api_external.uuid
from api_external
where type = ‘partnerApp’ and data.companyId = ‘70a149da27cc425da86cba890bf5b143’ ) as t4
JOIN (select api_external.data.env, api_external.data.partnerAppId
from api_external
where type = ‘integration’ ) as t5
ON t4.uuid = t5.partnerAppId
GROUP BY t4.uuid
)
SELECT t3.appName,
t3.uuid_proj as uuid,
t3.description,
ARRAY_AGG({t3.env,
t3.productStatus }) as productDetails
FROM ct3 AS t3
JOIN ct6 AS t6 ON t3.uuid_proj = t6.uuid_agg and t3.env = t6.env
group by t3.appName , t3.uuid_proj , t3.description ;

5

you mentioned product document document key is same as other field.
Is where api_external.type = ‘product’ and api_external.uuid
uuid is same as document key?
In that case change query

(select raw api_external.data.displayName
from api_external USE KEYS OBJECT_NAMES(t2.productStatus) where api_external.type = ‘product’ ) as productStatus

If not then you need CB 7.0 or need to do JOIN

6

It is the same but its appended by a constant string “product”:{product_key}. So I cant use the key directly.

7 
(select raw api_external.data.displayName
   from api_external USE KEYS (ARRAY "product:" || v FOR v IN OBJECT_NAMES(t2.productStatus)  END) ) as productStatus
8

@vsr1
I arrived at below N1QL from all the inputs
please observe the letting clause , for the lowest value of the env we are picking other details. with a change in the application we are getting multiple records for this criteria and we need to pick the one with max(t5.createdTs) . That is we need min(env) record with max time stamp. How do i change this N1QL
I tried using order by before letting it gave me syntax error

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
FROM api_external AS t4
JOIN api_external AS t5 ON t4.uuid = t5.data.partnerAppId
WHERE t4.type = "partnerApp"
       AND t4.data.companyId = '70a149da27cc425da86cba890bf5b143'
       AND t5.type = "integration"
       AND t5.data.partnerAppId IS NOT NULL
GROUP BY t4.uuid
LETTING m = MIN([t5.data.env, {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.env ,
ct3.dcrFlag,
ct3.createdTs,
(select  api_external.data.displayName as productName ,  uuid as productId
                    from api_external USE KEYS (ARRAY "product::" || v FOR v IN OBJECT_NAMES(ct3.productStatus)  END) ) as ProductDetails
from ct3
9

MIN is lowest MAX is highest.
convert createdTs to number and negatve so that it can be used in MIN

MIN([t5.data.env, -STR_TO_MILLIS(t5.createdTs), {t4.uuid, t4.data.appName, t4.data.description,
t5.data.env, t5.data.productStatus , t4.data.fourthParty , t4.uuid,t5.createdTs }]) )

change m[1] to m[2]

WITH ct3 AS ( SELECT m[2].*
              FROM api_external AS t4
              JOIN api_external AS t5 ON t4.uuid = t5.data.partnerAppId
              WHERE t4.type = "partnerApp"
                     AND t4.data.companyId = '70a149da27cc425da86cba890bf5b143'
                     AND t5.type = "integration"
                     AND t5.data.partnerAppId IS NOT NULL
              GROUP BY t4.uuid
              LETTING m = MIN([t5.data.env, -STR_TO_MILLIS(t5.createdTs), {"id": t4.uuid, "name": t4.data.appName, t4.data.description,
                                  t5.data.env, t5.data.productStatus , "dcrFlag": t4.data.fourthParty , t4.uuid,t5.createdTs }]) )
SELECT c.*,
       (  SELECT p.data.displayName AS productName, uuid AS productId
          FROM api_external AS p USE KEYS (ARRAY "product::" || v FOR v IN OBJECT_NAMES(c.productStatus)  END)
       ) AS ProductDetails
FROM ct3 AS c;
10

Can You suggest more optimal indexes for this ?
We are currently using below indexes but response is a bit slow . Wanted to know if any other indexes would make it faster

CREATE INDEX idx_companyId_appName_desc ON `api_external`((`data`.`companyId`),`uuid`,(`data`.`appName`),(`data`.`description`)) WHERE (`type` = 'partnerApp')
CREATE INDEX idx_company_integrationId ON `api_external`(`type`,(`data`.`partnerAppId`),`backupOf`)
CREATE INDEX idx_data_partnerAppId_type ON `api_external`((`data`.`partnerAppId`)) WHERE (`type` = 'integration')


SELECT m[2].appName as name , m[2].uuid as id ,m[2].description ,m[2].env , m[2].productStatus ,m[2].fourthParty as dcrFlag, 
m[2].createdTs as lastModified
FROM api_external AS t4
JOIN api_external AS t5 ON t4.uuid = t5.data.partnerAppId
WHERE t4.type = "partnerApp"
       AND t4.data.companyId = '70a149da27cc425da86cba890bf5b143'
       AND t5.type = "integration"
       AND t5.data.partnerAppId IS NOT NULL
GROUP BY t4.uuid
LETTING m = MIN([t5.data.env,-t5.createdTs, {t4.uuid, t4.data.appName, t4.data.description,
                    t5.data.env, t5.data.productStatus , t4.data.fourthParty , t4.uuid,t5.createdTs }])
11

Try these two:

CREATE INDEX ix1 ON `api_external`
(data.companyId,uuid,data.appName,data.description,data.fourthParty)
WHERE type = "partnerApp";

CREATE INDEX ix2 ON `api_external`
(data.partnerAppId,data.env,createdTs,data.productStatus)
WHERE type = "integration";

They should result in an entirely covered plan.

HTH.

1 Like