Skip to content

Instantly share code, notes, and snippets.

@gp187

gp187/FTS index definition for bucket contacts

Created April 24, 2020 07:03
Show Gist options
  • Save gp187/232fccb3269730c941f5d0daf1b522cb to your computer and use it in GitHub Desktop.
Save gp187/232fccb3269730c941f5d0daf1b522cb to your computer and use it in GitHub Desktop.
Download ZIP
Raw
example output AFTER running the same query a few times
[
{
"code": 5001,
"msg": "Panic: runtime error: invalid memory address or nil pointer dereference"
}
]
Raw
FTS index definition for bucket contacts
{
"type": "fulltext-index",
"name": "idx_listContactsSearch",
"uuid": "2394267d30241ef1",
"sourceType": "couchbase",
"sourceName": "contacts",
"sourceUUID": "b6f199faacce42c00290423d23f1e9a2",
"planParams": {
"maxPartitionsPerPIndex": 171,
"indexPartitions": 6
},
"params": {
"doc_config": {
"docid_prefix_delim": "",
"docid_regexp": "",
"mode": "type_field",
"type_field": "docType"
},
"mapping": {
"analysis": {},
"default_analyzer": "standard",
"default_datetime_parser": "dateTimeOptional",
"default_field": "_all",
"default_mapping": {
"dynamic": true,
"enabled": true
},
"default_type": "_default",
"docvalues_dynamic": true,
"index_dynamic": true,
"store_dynamic": false,
"type_field": "_type"
},
"store": {
"indexType": "scorch"
}
},
"sourceParams": {}
}
Raw
json array of example docs
[
{
"accessControl": [],
"userAccessList": [],
"groupAccessList": [],
"teamAccessList": [],
"userAccessListString": "",
"groupAccessListString": "",
"teamAccessListString": "",
"accessBy": "*",
"accessLog": [
{
"timestamp": "Thu Apr 23 2020 15:49:31 GMT+0000",
"type": "create",
"userId": "d2e8de7fc346426e8dfefb440dff9337"
}
],
"history": [],
"bookmark": "cb90838cd8112b441cb85565be2d4661",
"data": {
"avatarPath": "",
"comments": [],
"companyId": "",
"countryId": "",
"email": "[email protected]",
"facebook": "",
"gender": "Female",
"info": {
"firstName": "Tilda",
"lastName": "Hasell"
},
"jobPosition": "",
"languageId": "",
"linkedin": "",
"notes": "",
"phones": [],
"tags": [],
"twitter": "",
"website": ""
},
"docType": "contacts",
"namespace": "contacts",
"docId": "000175911ebc4cb9a1d40c37bfb70869",
"licenseId": "3802bb2b080f48adf03bb5a572b818b13bc0e9e6996a5111",
"deploymentId": "NAO",
"status": "active",
"ssoId": null,
"companyId": "378553108dfc4326bcc4696ff74cdcb7",
"flags": {},
"deleted": false,
"createdBy": "d2e8de7fc346426e8dfefb440dff9337",
"createdAt": "Thu Apr 23 2020 15:49:31 GMT+0000",
"updatedBy": "",
"updatedAt": null,
"deletedBy": "",
"deletedAt": null,
"dataPending": {}
},
{
"accessControl": [],
"userAccessList": [],
"groupAccessList": [],
"teamAccessList": [],
"userAccessListString": "",
"groupAccessListString": "",
"teamAccessListString": "",
"accessBy": "*",
"accessLog": [
{
"timestamp": "Wed Apr 22 2020 21:30:09 GMT+0300",
"type": "create",
"userId": "9eee1759395240ecbf316695020e550f"
}
],
"history": [],
"bookmark": "8805cc88d795922f8fbe63165b41435e",
"data": {
"avatarPath": "http://dummyimage.com/212x238.png/dddddd/000000",
"comments": [],
"companyId": "",
"countryId": "",
"email": "[email protected]",
"facebook": "",
"gender": "",
"info": {
"firstName": "Randi",
"lastName": "Pauly"
},
"jobPosition": "",
"languageId": "",
"linkedin": "",
"notes": "",
"phones": [],
"tags": [],
"twitter": "",
"website": "com.pcworld.Ventosanzap"
},
"docType": "contacts",
"namespace": "contacts",
"docId": "00203ff778fa458f85e7068d3f09478a",
"licenseId": "c81f834f4e248cf64c6d5e70d06ae0b1d610ca0226eb1aa7",
"deploymentId": "NAO",
"status": "active",
"ssoId": null,
"companyId": "9468ba09f6fe4f078e1352012abb6644",
"flags": {},
"deleted": false,
"createdBy": "9eee1759395240ecbf316695020e550f",
"createdAt": "Wed Apr 22 2020 21:30:09 GMT+0300",
"updatedBy": "",
"updatedAt": null,
"deletedBy": "",
"deletedAt": null,
"dataPending": {}
},
{
"accessControl": [],
"userAccessList": [],
"groupAccessList": [],
"teamAccessList": [],
"userAccessListString": "",
"groupAccessListString": "",
"teamAccessListString": "",
"accessBy": "*",
"accessLog": [
{
"timestamp": "Thu Apr 23 2020 15:49:30 GMT+0000",
"type": "create",
"userId": "d2e8de7fc346426e8dfefb440dff9337"
}
],
"history": [],
"bookmark": "a958383c1edff5aff9091ce87e81aaf3",
"data": {
"avatarPath": "",
"comments": [],
"companyId": "",
"countryId": "",
"email": "[email protected]",
"facebook": "",
"gender": "Female",
"info": {
"firstName": "Sandi",
"lastName": "Tofanelli"
},
"jobPosition": "",
"languageId": "",
"linkedin": "",
"notes": "",
"phones": [],
"tags": [],
"twitter": "",
"website": ""
},
"docType": "contacts",
"namespace": "contacts",
"docId": "0025034e6a5749a1aa0bffdfd2f23449",
"licenseId": "3802bb2b080f48adf03bb5a572b818b13bc0e9e6996a5111",
"deploymentId": "NAO",
"status": "active",
"ssoId": null,
"companyId": "378553108dfc4326bcc4696ff74cdcb7",
"flags": {},
"deleted": false,
"createdBy": "d2e8de7fc346426e8dfefb440dff9337",
"createdAt": "Thu Apr 23 2020 15:49:30 GMT+0000",
"updatedBy": "",
"updatedAt": null,
"deletedBy": "",
"deletedAt": null,
"dataPending": {}
},
{
"accessControl": [],
"userAccessList": [],
"groupAccessList": [],
"teamAccessList": [],
"userAccessListString": "",
"groupAccessListString": "",
"teamAccessListString": "",
"accessBy": "*",
"accessLog": [
{
"timestamp": "Thu Apr 23 2020 15:49:31 GMT+0000",
"type": "create",
"userId": "d2e8de7fc346426e8dfefb440dff9337"
}
],
"history": [],
"bookmark": "7495f8a395877787775104ee8c6cc134",
"data": {
"avatarPath": "",
"comments": [],
"companyId": "",
"countryId": "",
"email": "[email protected]",
"facebook": "",
"gender": "Female",
"info": {
"firstName": "Willow",
"lastName": "Allmond"
},
"jobPosition": "",
"languageId": "",
"linkedin": "",
"notes": "",
"phones": [],
"tags": [],
"twitter": "",
"website": ""
},
"docType": "contacts",
"namespace": "contacts",
"docId": "002c2cb72de248d685eefe33192ad145",
"licenseId": "3802bb2b080f48adf03bb5a572b818b13bc0e9e6996a5111",
"deploymentId": "NAO",
"status": "active",
"ssoId": null,
"companyId": "378553108dfc4326bcc4696ff74cdcb7",
"flags": {},
"deleted": false,
"createdBy": "d2e8de7fc346426e8dfefb440dff9337",
"createdAt": "Thu Apr 23 2020 15:49:31 GMT+0000",
"updatedBy": "",
"updatedAt": null,
"deletedBy": "",
"deletedAt": null,
"dataPending": {}
},
{
"accessControl": [],
"userAccessList": [],
"groupAccessList": [],
"teamAccessList": [],
"userAccessListString": "",
"groupAccessListString": "",
"teamAccessListString": "",
"accessBy": "*",
"accessLog": [
{
"timestamp": "Thu Apr 23 2020 16:07:16 GMT+0000",
"type": "create",
"userId": "d2e8de7fc346426e8dfefb440dff9337"
}
],
"history": [],
"bookmark": "9fa2322f05dcb65c2398b44a3d658e49",
"data": {
"avatarPath": "",
"comments": [],
"companyId": "",
"countryId": "",
"email": "[email protected]",
"facebook": "",
"gender": "Male",
"info": {
"firstName": "Emmy",
"lastName": "Warlow"
},
"jobPosition": "",
"languageId": "",
"linkedin": "",
"notes": "",
"phones": [],
"tags": [],
"twitter": "",
"website": ""
},
"docType": "contacts",
"namespace": "contacts",
"docId": "004299288742415c87c8f00a7afebe35",
"licenseId": "3802bb2b080f48adf03bb5a572b818b13bc0e9e6996a5111",
"deploymentId": "NAO",
"status": "active",
"ssoId": null,
"companyId": "378553108dfc4326bcc4696ff74cdcb7",
"flags": {},
"deleted": false,
"createdBy": "d2e8de7fc346426e8dfefb440dff9337",
"createdAt": "Thu Apr 23 2020 16:07:16 GMT+0000",
"updatedBy": "",
"updatedAt": null,
"deletedBy": "",
"deletedAt": null,
"dataPending": {}
},
{
"accessControl": [],
"userAccessList": [],
"groupAccessList": [],
"teamAccessList": [],
"userAccessListString": "",
"groupAccessListString": "",
"teamAccessListString": "",
"accessBy": "*",
"accessLog": [
{
"timestamp": "Thu Apr 16 2020 11:53:42 GMT+0300",
"type": "create",
"userId": "9eee1759395240ecbf316695020e550f"
}
],
"history": [],
"bookmark": "ec4adef50a5092f5c7983e1dbee48b11",
"data": {
"file": {
"tempFile": {
"source": {
"fieldname": "files",
"filename": "MOCK_DATA (1).csv",
"encoding": "7bit",
"mimetype": "application/vnd.ms-excel"
},
"files": [],
"temp": {
"path": "C:\\Users\\Florin\\AppData\\Local\\Temp\\3b11736b5c3deb0f62cfc453.csv",
"name": "3b11736b5c3deb0f62cfc453.csv",
"dir": "C:\\Users\\Florin\\AppData\\Local\\Temp",
"fileName": "3b11736b5c3deb0f62cfc453"
}
},
"remoteFile": {
"tag": "\"bcb35c06c7860404222a632e6a1ad12f\"",
"absPath": "company\\5ed108ac8d0d5703daff3395\\3b11736b5c3deb0f62cfc453.csv",
"path": "5ed108ac8d0d5703daff3395",
"relativePath": "3b11736b5c3deb0f62cfc453.csv",
"cdnPath": "company-cdn:3b11736b5c3deb0f62cfc453.csv"
}
},
"doc": {
"avatarPath": "",
"companyId": "",
"countryId": "",
"email": "",
"facebook": "",
"gender": "",
"info": {
"firstName": "",
"lastName": ""
},
"jobPosition": "",
"languageId": "",
"linkedin": "",
"notes": "",
"twitter": "",
"website": ""
},
"docFlat": [
"avatarPath",
"companyId",
"countryId",
"email",
"facebook",
"gender",
"info.firstName",
"info.lastName",
"jobPosition",
"languageId",
"linkedin",
"notes",
"twitter",
"website"
],
"match": [],
"columns": [
{
"i": 0,
"index": "id",
"name": ""
},
{
"i": 1,
"index": "info.firstName",
"name": ""
},
{
"i": 2,
"index": "info.lastName",
"name": ""
},
{
"i": 3,
"index": "email",
"name": ""
},
{
"i": 4,
"index": "gender",
"name": ""
},
{
"i": 5,
"index": "jobPosition_smecher",
"name": ""
},
{
"i": 6,
"index": "avatarPath",
"name": ""
},
{
"i": 7,
"index": "phones_data",
"name": ""
},
{
"i": 8,
"index": "coments_data",
"name": ""
},
{
"i": 9,
"index": "website",
"name": ""
},
{
"i": 10,
"index": "countryId",
"name": ""
}
],
"rows": [
{
"id": 1,
"info.firstName": "Dorri",
"info.lastName": "Doull",
"email": "[email protected]",
"jobPosition_smecher": "Community Outreach Specialist",
"avatarPath": "http://dummyimage.com/185x159.jpg/dddddd/000000",
"phones_data": "[{},{},{},{},{}]",
"countryId": "Greece"
},
{
"id": 2,
"info.firstName": "Daisy",
"info.lastName": "Maseyk",
"email": "[email protected]",
"jobPosition_smecher": "Geological Engineer",
"phones_data": "[{},{}]"
},
{
"id": 3,
"info.firstName": "Chrissie",
"info.lastName": "Van Waadenburg",
"email": "[email protected]",
"jobPosition_smecher": "Safety Technician II",
"avatarPath": "http://dummyimage.com/205x134.bmp/ff4444/ffffff",
"phones_data": "[{},{},{},{},{}]",
"coments_data": "[{},{},{}]",
"countryId": "Ukraine"
},
{
"id": 4,
"info.firstName": "Dimitri",
"info.lastName": "Alldread",
"email": "[email protected]",
"gender": "Male",
"avatarPath": "http://dummyimage.com/111x123.png/ff4444/ffffff",
"website": "jp.japanpost.Sub-Ex",
"countryId": "Colombia"
},
{
"id": 5,
"info.firstName": "Nikolas",
"info.lastName": "Nehlsen",
"email": "[email protected]",
"jobPosition_smecher": "Actuary",
"avatarPath": "http://dummyimage.com/234x159.bmp/5fa2dd/ffffff",
"phones_data": "[{},{},{}]",
"coments_data": "[{},{}]",
"countryId": "Brazil"
},
{
"id": 6,
"info.firstName": "Donalt",
"info.lastName": "Cottell",
"email": "[email protected]",
"jobPosition_smecher": "Cost Accountant",
"phones_data": "[{}]",
"coments_data": "[{},{},{}]",
"countryId": "Ukraine"
},
{
"id": 7,
"info.firstName": "Henrietta",
"info.lastName": "Rhys",
"email": "[email protected]",
"gender": "Female",
"jobPosition_smecher": "Staff Scientist",
"avatarPath": "http://dummyimage.com/237x156.png/ff4444/ffffff",
"phones_data": "[{},{},{},{}]",
"website": "eu.europa.Lotlux",
"countryId": "Brazil"
},
{
"id": 8,
"info.firstName": "Bordy",
"info.lastName": "Lermouth",
"email": "[email protected]",
"avatarPath": "http://dummyimage.com/213x175.png/5fa2dd/ffffff",
"countryId": "Sweden"
},
{
"id": 9,
"info.firstName": "Jervis",
"info.lastName": "Dash",
"email": "[email protected]",
"gender": "Male",
"avatarPath": "http://dummyimage.com/245x191.png/dddddd/000000",
"coments_data": "[{},{},{},{},{}]",
"website": "cz.mapy.Gembucket",
"countryId": "Honduras"
},
{
"id": 10,
"info.firstName": "Zarla",
"info.lastName": "McNickle",
"email": "[email protected]",
"gender": "Female",
"jobPosition_smecher": "Associate Professor",
"avatarPath": "http://dummyimage.com/227x102.png/dddddd/000000",
"phones_data": "[{}]",
"website": "ru.odnoklassniki.Keylex",
"countryId": "China"
},
{
"id": 11,
"info.firstName": "Bjorn",
"info.lastName": "Craise",
"email": "[email protected]",
"jobPosition_smecher": "Health Coach II",
"avatarPath": "http://dummyimage.com/133x154.bmp/dddddd/000000",
"phones_data": "[{},{}]",
"countryId": "Colombia"
},
{
"id": 12,
"info.firstName": "Sharline",
"info.lastName": "Whawell",
"email": "[email protected]",
"jobPosition_smecher": "Structural Analysis Engineer",
"phones_data": "[{},{}]",
"coments_data": "[{},{},{},{}]",
"countryId": "Philippines"
},
{
"id": 13,
"info.firstName": "Barth",
"info.lastName": "D'Alesco",
"email": "[email protected]",
"jobPosition_smecher": "Geologist I",
"phones_data": "[{},{}]",
"countryId": "China"
},
{
"id": 14,
"info.firstName": "Dylan",
"info.lastName": "Swindells",
"email": "[email protected]",
"gender": "Male",
"avatarPath": "http://dummyimage.com/214x183.png/ff4444/ffffff",
"website": "org.un.Job",
"countryId": "China"
},
{
"id": 15,
"info.firstName": "Derrek",
"info.lastName": "Hilldrup",
"email": "[email protected]",
"gender": "Male",
"website": "com.nymag.Zathin"
},
{
"id": 16,
"info.firstName": "Deck",
"info.lastName": "Taplow",
"email": "[email protected]",
"jobPosition_smecher": "Civil Engineer",
"phones_data": "[{}]",
"coments_data": "[{},{},{},{}]",
"countryId": "China"
},
{
"id": 17,
"info.firstName": "Sibbie",
"info.lastName": "Soames",
"email": "[email protected]",
"jobPosition_smecher": "Database Administrator III",
"phones_data": "[{},{},{},{},{}]",
"coments_data": "[{}]",
"countryId": "China"
},
{
"id": 18,
"info.firstName": "Allix",
"info.lastName": "Rossborough",
"email": "[email protected]",
"gender": "Female",
"avatarPath": "http://dummyimage.com/149x170.jpg/5fa2dd/ffffff",
"coments_data": "[{},{},{}]",
"website": "com.ted.Alpha",
"countryId": "Ukraine"
},
{
"id": 19,
"info.firstName": "Clayborne",
"info.lastName": "Hoyles",
"email": "[email protected]",
"gender": "Male",
"jobPosition_smecher": "Compensation Analyst",
"avatarPath": "http://dummyimage.com/178x164.jpg/5fa2dd/ffffff",
"phones_data": "[{}]",
"coments_data": "[{},{}]",
"website": "com.feedburner.Regrant",
"countryId": "Malaysia"
},
{
"id": 20,
"info.firstName": "Boot",
"info.lastName": "Follan",
"email": "[email protected]",
"jobPosition_smecher": "Actuary",
"avatarPath": "http://dummyimage.com/133x123.bmp/5fa2dd/ffffff",
"phones_data": "[{}]",
"coments_data": "[{},{},{},{}]",
"countryId": "Russia"
}
]
},
"docType": "import",
"namespace": "contacts",
"docId": "0030a650294e407384f6c0975dead8f3",
"licenseId": "c81f834f4e248cf64c6d5e70d06ae0b1d610ca0226eb1aa7",
"deploymentId": "NAO",
"status": "active",
"ssoId": null,
"companyId": "9468ba09f6fe4f078e1352012abb6644",
"flags": {},
"deleted": false,
"createdBy": "9eee1759395240ecbf316695020e550f",
"createdAt": "Thu Apr 16 2020 11:53:42 GMT+0300",
"updatedBy": "",
"updatedAt": null,
"deletedBy": "",
"deletedAt": null,
"dataPending": {}
}
]
Raw
query
SELECT searchData, countData LET searchData=(SELECT meta().id,`docId`,`createdAt`,`updatedAt`,`data` FROM contacts AS ccc WHERE `deploymentId` = 'NAO' AND `licenseId` = '3802bb2b080f48adf03bb5a572b818b13bc0e9e6996a5111' AND `docType` = 'contacts' AND status = 'active' AND `namespace` = 'contacts' AND `companyId` = '378553108dfc4326bcc4696ff74cdcb7' LIMIT 10 OFFSET 0), countData=(SELECT RAW COUNT(meta().id) FROM contacts AS ccc WHERE `deploymentId` = 'NAO' AND `licenseId` = '3802bb2b080f48adf03bb5a572b818b13bc0e9e6996a5111' AND `docType` = 'contacts' AND status = 'active' AND `namespace` = 'contacts' AND `companyId` = '378553108dfc4326bcc4696ff74cdcb7')[0]
Raw
query explain
{
"#operator": "Sequence",
"~children": [
{
"#operator": "DummyScan"
},
{
"#operator": "Parallel",
"maxParallelism": 1,
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Let",
"bindings": [
{
"expr": "(select (meta(`ccc`).`id`), (`ccc`.`docId`), (`ccc`.`createdAt`), (`ccc`.`updatedAt`), (`ccc`.`data`) from `contacts` as `ccc` where (((((((`ccc`.`deploymentId`) = \"NAO\") and ((`ccc`.`licenseId`) = \"3802bb2b080f48adf03bb5a572b818b13bc0e9e6996a5111\")) and ((`ccc`.`docType`) = \"contacts\")) and ((`ccc`.`status`) = \"active\")) and ((`ccc`.`namespace`) = \"contacts\")) and ((`ccc`.`companyId`) = \"378553108dfc4326bcc4696ff74cdcb7\")) limit 10 offset 0)",
"var": "searchData"
},
{
"expr": "((select raw count((meta(`ccc`).`id`)) from `contacts` as `ccc` where (((((((`ccc`.`deploymentId`) = \"NAO\") and ((`ccc`.`licenseId`) = \"3802bb2b080f48adf03bb5a572b818b13bc0e9e6996a5111\")) and ((`ccc`.`docType`) = \"contacts\")) and ((`ccc`.`status`) = \"active\")) and ((`ccc`.`namespace`) = \"contacts\")) and ((`ccc`.`companyId`) = \"378553108dfc4326bcc4696ff74cdcb7\")))[0])",
"var": "countData"
}
]
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "`searchData`"
},
{
"expr": "`countData`"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
}
@gp187
Copy link
Author

gp187 commented Apr 24, 2020

The point of the query is to search and count at the same time because when the user filters data by some queries. We had to because N1QL doesn't return total hits as FTS does and the combination between the 2 is perfect for our use case.

UPDATE:
I can confirm, separating the query into 2 different database calls doesn't produce that error.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment