Need help in writing the N1QL query

SQL++
,
1

Hi

I have 2 types of documents in the database

[{
   "admins": [
     "2dd72827f7e948cc9257cd9fa6dc3f7a"
   ],
   "companyName": "ABC - company",
   "developers": [
     "2dd72827f7e948cc9257cd9fa6dc3f7a"
   ],
   "uuid": "0001bb842f974fb6947b744be48b2a68",
   "type" : "company"
 },
 {
   "admins": [
     "d2054b62bb614db3bed6d26ad71b14b8",
     "c6eb89e4242d4bf2a746d1afcd869c64",
     "846fc06363d64c55b318256cb53c3f58"
   ],
   "companyName": "CDE company",
   "developers": [
     "2feb377cfc504bd0870aba3027bc4893",
     "d2054b62bb614db3bed6d26ad71b14b8",
     "c6eb89e4242d4bf2a746d1afcd869c64",
     "846fc06363d64c55b318256cb53c3f58"
   ],
   "uuid": "0007bd40b4bb4651a4381aad31cd7bb6",
   "type" : "company"
 }
 ]

And 2nd type of document is

  [
	  { 
	  "uuid" : "2dd72827f7e948cc9257cd9fa6dc3f7a",
	  "name" : "someUser" 
	  "email" :abc@xyz.com" 
	  "type" : "user" 
	  } 
  ]

I Want to create a table or a json array with the following details

[
{ userName ,
companyName,
email
}
]

If user is in multiple companies I want multiple records

[
{ userName : “abc” ,
companyName : “xyz”,
email :"abc@xyz.com
} ,
{ userName : “abc” ,
companyName : “lmn”,
email :"abc@lmn.com
}

]

The bucket name is api_external and all documents are in the bucket.
Can you tell me how i can write this N1QL . We have an index on the document type

2

Ideally with:

CREATE INDEX ix1 ON `api_external`(`type`,`uuid`,`name`,`email`);

or

CREATE INDEX ix1 ON `api_external`(`type`,`uuid`,`name`,`email`) WHERE type = "user";
CREATE INDEX ix2 ON `api_external`(`type`,`uuid`,`name`,`email`) WHERE type = "company";

(but will work with only an index on type) you can try:

SELECT u.name userName
      ,c.companyName
      ,u.email
FROM api_external u, api_external c
WHERE u.type = "user"
AND c.type = "company"
AND (
    ANY a IN c.admins SATISFIES a = u.uuid END
    OR
    ANY d IN c.developers SATISFIES d = u.uuid END
    )

If it is your preference or your version is out of date you’ll can use the ANSI join syntax instead:

SELECT u.name userName
      ,c.companyName
      ,u.email
FROM api_external u JOIN api_external c ON 
    (
    ANY a IN c.admins SATISFIES a = u.uuid END
    OR
    ANY d IN c.developers SATISFIES d = u.uuid END
    )
WHERE u.type = "user"
AND c.type = "company"

Ref:
https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/join.html
https://query-tutorial.couchbase.com/tutorial/#12

HTH.

1 Like
3

Of course I should add, the e-mail is a single attribute of the user - all records for that user will have the same e-mail. Your data model would have to differ to support a unique e-mail per company.

6

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.