How to write this n1ql

SQL++
,
1

Provided I have a document like

[{
   "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"
 }
 ]

We want to create one record for each combination developers or admins


[
{
CompanyUuid : "0007bd40b4bb4651a4381aad31cd7bb6",
DevOrAdmin : "d2054b62bb614db3bed6d26ad71b14b8"
},
{
CompanyUuid :"0007bd40b4bb4651a4381aad31cd7bb6",
DevOrAdmin: "2feb377cfc504bd0870aba3027bc4893"
}
.
.
.
]

Basically we need one record for each unique developers or admins from the company record type
How do we write n1ql for the same ?

2

Here is a simple possibility with UNNEST:

SELECT DISTINCT devOrAdmin, b.uuid companyUuid
FROM theBucket b
UNNEST array_concat(admins,developers) AS devOrAdmin

or you could use ARRAY_UNION:

SELECT devOrAdmin, b.uuid companyUuid
FROM theBucket b
UNNEST array_union(admins,developers) AS devOrAdmin

etc.

1 Like