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
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • 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": "thasello3@1688.com",
"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": "rpauly12@shinystat.com",
"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": "stofanelli4u@japanpost.jp",
"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": "wallmondlk@acquirethisname.com",
"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": "ewarlowjn@mozilla.org",
"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": "ddoull0@creativecommons.org",
"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": "dmaseyk1@aboutads.info",
"jobPosition_smecher": "Geological Engineer",
"phones_data": "[{},{}]"
},
{
"id": 3,
"info.firstName": "Chrissie",
"info.lastName": "Van Waadenburg",
"email": "cvanwaadenburg2@va.gov",
"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": "dalldread3@about.me",
"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": "nnehlsen4@hugedomains.com",
"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": "dcottell5@baidu.com",
"jobPosition_smecher": "Cost Accountant",
"phones_data": "[{}]",
"coments_data": "[{},{},{}]",
"countryId": "Ukraine"
},
{
"id": 7,
"info.firstName": "Henrietta",
"info.lastName": "Rhys",
"email": "hrhys6@ebay.com",
"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": "blermouth7@163.com",
"avatarPath": "http://dummyimage.com/213x175.png/5fa2dd/ffffff",
"countryId": "Sweden"
},
{
"id": 9,
"info.firstName": "Jervis",
"info.lastName": "Dash",
"email": "jdash8@who.int",
"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": "zmcnickle9@delicious.com",
"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": "bcraisea@cloudflare.com",
"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": "swhawellb@google.it",
"jobPosition_smecher": "Structural Analysis Engineer",
"phones_data": "[{},{}]",
"coments_data": "[{},{},{},{}]",
"countryId": "Philippines"
},
{
"id": 13,
"info.firstName": "Barth",
"info.lastName": "D'Alesco",
"email": "bdalescoc@wikispaces.com",
"jobPosition_smecher": "Geologist I",
"phones_data": "[{},{}]",
"countryId": "China"
},
{
"id": 14,
"info.firstName": "Dylan",
"info.lastName": "Swindells",
"email": "dswindellsd@elpais.com",
"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": "dhilldrupe@ucsd.edu",
"gender": "Male",
"website": "com.nymag.Zathin"
},
{
"id": 16,
"info.firstName": "Deck",
"info.lastName": "Taplow",
"email": "dtaplowf@cpanel.net",
"jobPosition_smecher": "Civil Engineer",
"phones_data": "[{}]",
"coments_data": "[{},{},{},{}]",
"countryId": "China"
},
{
"id": 17,
"info.firstName": "Sibbie",
"info.lastName": "Soames",
"email": "ssoamesg@dion.ne.jp",
"jobPosition_smecher": "Database Administrator III",
"phones_data": "[{},{},{},{},{}]",
"coments_data": "[{}]",
"countryId": "China"
},
{
"id": 18,
"info.firstName": "Allix",
"info.lastName": "Rossborough",
"email": "arossboroughh@zimbio.com",
"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": "choylesi@theguardian.com",
"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": "bfollanj@squidoo.com",
"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