Working in Query Array in CB DB

SQL++
1

Hello All,
following are my document structure and I have to query in CB DB for the fields in where condition for DeptCode/RoleCode. I am new early reply is much appreciable.

[{
	"Lang" : "en_US",
	"DocType" : "Role Permission Document", 
    "DeptCode" : "FOE",
	"RoleCode" : "HOD",
	"Fnctn" : [{
		"Text" : "Use payment functionality",
		"Oper" : {
			"Add": 1
			}
             }],
	"SubFnctn" : [{
			"Text" : "Cash Payment",
			"Oper" : {
				"Add": 1
				}
                       }]
},
{
	"Lang" : "en_US",
	"DocType" : "Role Permission Document", 
        "DeptCode" : "FD",
	"RoleCode" : "HOD",
	"Fnctn" : [{
		"Text" : "Add User Functionality",
		"Oper" : {
			"Mod": 1,
			"Cncl": 0,
			}
                    }],
	"SubFnctn" : [{
			"Text" : "Delete User Functionality",
			"Oper" : {
				"Del": 0
				}
                 }]
}]
2

If the array filed in the document us a1

SELECT   du.* FROM default AS d UNNEST d.a1 AS du 
WHERE du.DeptCode = "FOE" AND RoleCode = "HOD";

https://docs.couchbase.com/server/5.5/n1ql/n1ql-language-reference/indexing-arrays.html

Also Checkout “Where” clause inside array not working

https://query-tutorial.couchbase.com/tutorial/#1

3

I am really looking for above document type solution. proposed solution will not work. Please also tell me can we apply meta().id along with unnest. Kindly revert

4

Please post the original document as it is, expected output and explain what exactly you are looking.

5

below is my original document whose meta().id= “RolePermissions”
I want to query the document “RolePermissions” in Test Bucket
with DeptCode and RoleCode.
If you feel this is not best Json format you can also propose new json format to get the result I required.
[{
“Lang” : “en_US”,
“DocType” : “Role Permission Document”,
“DeptCode” : “FOE”,
“RoleCode” : “HOD”,
“Level” : “LevelOne”,
“Fnctn” : {
“Text” : “Use payment functionality”,
“Type” : “Generic” ,
“Link” : “PaymentEntity”,
“Oper” : {
“Add”: 1,
“Mod”: 1,
“Cncl”: 0,
“Del”: 0,
“Rfund”: 1
}
},
“SubFnctn” : {
“Text” : “Cash Payment”,
“Type” : “Optional”,
“Link” : “PaymentEntity”,
“Oper” : {
“Add”: 1,
“Mod”: 1,
“Cncl”: 0,
“Del”: 0,
“Rfund”: 1
}
}
},
{
“Lang” : “en_US”,
“DocType” : “Role Permission Document”,
“DeptCode” : “FD”,
“RoleCode” : “HOD”,
“Level” : “LevelOne”,
“Fnctn” : {
“Text” : “Add User Functionality”,
“Type” : “Generic” ,
“Link” : “AddUser”,
“Oper” : {
“Add”: 1,
“Mod”: 1,
“Cncl”: 0,
“Del”: 0,
“Rfund”: 1
}
},
“SubFnctn” : {
“Text” : “Delete User Functionality”,
“Type” : “Optional”,
“Link” : “DeleteUser”,
“Oper” : {
“Add”: 1,
“Mod”: 1,
“Cncl”: 0,
“Del”: 0,
“Rfund”: 1
}
}
}
]

6

What is your expected output? The query proposed earlier should work. you mentioned it doesn’t work and did not say why?

If your bucket is default and document id is “RolePermissions”

   SELECT d.* 
   FROM default AS d USE KEYS ["RolePermissions"];

If you want extract DeptCode, RoleCode when DocType = “Role Permission Document” from original document as array of objects

   SELECT  ARRAY {v.DeptCode, v.RoleCode} FOR v IN d WHEN v.DocType = "Role Permission Document" END AS newdoc
   FROM default AS d USE KEYS ["RolePermissions"];

If you want extract DeptCode, RoleCode when DocType = “Role Permission Document” from original document as separate documents

   SELECT  du.DeptCode, du.RoleCode
   FROM default AS d USE KEYS ["RolePermissions"]
   UNNEST d AS du 
   WHERE du.DocType = "Role Permission Document" ;
7

perfectly working thanks a lots.

my working query
SELECT du.* FROM APTCREF AS d USE KEYS[‘RolePermissions’]
UNNEST d.RolePermission AS du where du.DeptCode = ‘FOE’ AND du.RoleCode = ‘HOD’