Searching the documents

Mobile Couchbase Lite
1

I need to implement the search functionality for the document having around 25 attributes. In quick search the search token need to be matched with/found in 5 properties of the document. And in advanced search the documents are searched based on 10 different parameters from the document properties.

I tried by emitting multiple keys, emitting the array of keys but couldn’t help it. In both the case the view returns 0 rows.

What is the best way to implement the search feature?

Environment details -

Couchbase Lite - 1.3.1
Xamarin - 4.2.2.6
Xamarin Android - 7.0.2.42
Xamarin Forms - 10.3.1.7

Thanks.

2

Why don’t you show us the code you wrote, and what your documents look like?

3

Following is our document sample -

{
"_id": “asset::021d7aa1-8d1c-4a04-8d25-c09f4a4ab4fc”,
"_rev": “4-20f98b257c66403033b9891af66cd36f”,
“assetBarcode”: null,
“assetDescription”: “Jet MM-0300 3T Lever Tool”,
“assetDiagram”: null,
“assetGPSLat”: null,
“assetGPSLong”: null,
“assetImage”: “080b6c60-b4ac-4b64-b20e-72ff3fc21c03”,
“assetLocation”: {
“id”: “location::54101::287308”,
“name”: “Site”,
“parentId”: “location::54101::78a93935-2124-4e51-9b21-f61c44fb75a0”
},
“assetNumber”: “AD-Asset#w76”,
“attributes”: [
{
“attributeId”: “attribute::3119”,
“attributeName”: “Hoist Type”,
“attributeType”: “TEXT50”,
“attributeValue”: “Chain Lever Hoist”
},
{
“attributeId”: “attribute::3121”,
“attributeName”: “Serial Number”,
“attributeType”: “TEXT50”,
“attributeValue”: “”
},
{
“attributeId”: “attribute::16691”,
“attributeName”: “Hoist Manufacturer”,
“attributeType”: “TEXT50”,
“attributeValue”: “Jet”
},
{
“attributeId”: “attribute::3120”,
“attributeName”: "Model ",
“attributeType”: “TEXT50”,
“attributeValue”: “MM”
},
{
“attributeId”: “attribute::3123”,
“attributeName”: “Capacity Tons”,
“attributeType”: “TEXT50”,
“attributeValue”: “3”
},
{
“attributeId”: “attribute::10444”,
“attributeName”: “PO #”,
“attributeType”: “TEXT50”,
“attributeValue”: “”
},
{
“attributeId”: “attribute::3124”,
“attributeName”: “Capacity Lbs”,
“attributeType”: “TEXT50”,
“attributeValue”: “6000”
},
{
“attributeId”: “attribute::3125”,
“attributeName”: “No. of Chain Falls”,
“attributeType”: “TEXT50”,
“attributeValue”: “1”
},
{
“attributeId”: “attribute::3122”,
“attributeName”: “Lift-Ft”,
“attributeType”: “TEXT50”,
“attributeValue”: “”
}
],
“category”: {
“categoryId”: “category::13616”,
“categoryName”: “Hoist and Puller”,
“icoSourceCat”: “0”,
“legacyDontUse”: true
},
“channels”: [
“64.SHRW”
],
“chipIds”: null,
“createdByTID”: 64,
“createdDate”: “2017-06-17T09:38:50.004604Z”,
“createdDateTicks”: -636332891300046080,
“dataOwnerTID”: 0,
“expiryDate”: “2017-06-29T11:30:00+05:30”,
“files”: [
{
“Labels”: “Diagram”,
“azureBlobUID”: “b838c19c-e806-4a85-a2eb-3c9792b7c1e0”,
“containerName”: null,
“description”: “test”,
“effectiveDate”: “2017-06-17T11:30:00+05:30”,
“fileName”: “06172017033721.jpg”,
“filePath”: “/data/data/com.TESSALink.Android/files/InfoChip/64/Upload/06172017033721.jpg”,
“label”: [
“Diagram”
]
}
],
“filledFormSummary”: [
{
“completedDate”: “2017-06-17T12:32:21.224172Z”,
“formDocumentId”: “filledForm::b38786fd-dbfd-4748-86de-7d64353257a0”,
“formId”: “form::64::-1”,
“formType”: “Scrap”,
“name”: “Scrap Form”,
“result”: “FAIL”
}
],
“inServiceDate”: “2017-06-17T11:30:00+05:30”,
“isDeleted”: false,
“lastCompletedWorkDate”: null,
“modelAndPart”: {
“modelId”: null,
“modelNumber”: null,
“partDescription”: null,
“partId”: null,
“partNumber”: null
},
“modifiedDate”: “2017-06-17T09:38:50.004554Z”,
“nextCompletedWorkDate”: null,
“outServiceDate”: null,
“owner”: {
“id”: “tenant::54101”,
“name”: “C & A STEEL (1983) Ltd.”
},
“parentId”: null,
“rentalStatus”: null,
“serialNumber”: “Ser#w76”,
“status”: “Scrapped”,
“tenantId”: “tenant::64”,
“thumbnailImage”: null,
“type”: “asset”,
“version”: null
}

We need to perform quick search on following properties -
assetNumber,
serialNumber,
assetBarcode,
chipIds (- this is string array).

For advance search properties are -

assetNumber,
assetDescription,
category,
owner,
assetLocation,
serialNumber,
assetBarcode,
chipIds,
createdDate,
status,
expiryDate,
inServiceDate,
modelAndPart.

We created following view -

{
MapDelegate assetViewFilterMapDelegate = (doc, emit) =>
{
try
{
if (doc["_id"].ToString().StartsWith(Constants.AssetDocumentID))
{
List keys = new List();
keys.Add(doc[“assetNumber”]);
keys.Add(doc[“serialNumber”]);
keys.Add(doc[“assetBarcode”]);
keys.Add(doc[“chipIds”]);
emit(keys, doc);
}
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
}
};

var allAssetsView = database.GetView(Constants.AllAssetsView);
allAssetsView.SetMap(assetViewFilterMapDelegate, "1");

}

Following is the query created to fetch the data -

public List GetAllDocuments(string viewName, List keys, int startPosition = -1, int recordCount = Constants.MaxDefaultRecordsToFetch)
{
var objRetValue = new List();

try {
	if (null != database) {
		var assetsDocsView = database.GetView(viewName);
		var query = assetsDocsView.CreateQuery();

		List<Object> allKeys = new List<Object>();

		if (startPosition >= 0) {
			query.Skip = startPosition;
		}

		allKeys.Add(keys);
		query.Keys = allKeys;
		query.Limit = recordCount > 0 ? recordCount : query.Limit;

		var rows = query.Run();

		objRetValue = rows.Select(result => JsonConvert.DeserializeObject<T>(JsonConvert.SerializeObject(result.Value, Formatting.Indented))).ToList();
	}
} catch (Exception e) {
	Debug.WriteLine(e.Message);
}

return objRetValue;

}

We call the GetAllDocuments as -

List allKeys = new List();
allKeys.Add(“searchToken”);
allKeys.Add(“searchToken”);
allKeys.Add(“searchToken”);
allKeys.Add(“searchToken”);

List searchedAssets = GetAllDocuments(Constants.AllAssetsView, allKeys) ;

4

Have you read the view and query documentation carefully? That is not how arrays work in a key.

The map function is creating an index, and the keys you emit become the keys in that index. If you want to find a document under multiple keys, you need to emit each key independently, so there will be multiple index entries referring to that document under each key. Then whichever of those keys you search for, the doc will be found.

5

We tried this as well -

{
MapDelegate assetViewFilterMapDelegate = (doc, emit) =>
{
try
{
if (doc["_id"].ToString().StartsWith(Constants.AssetDocumentID))
{
emit(doc["assetNumber"], doc);	
emit(doc["serialNumber"], doc);	
emit(doc["assetBarcode"], doc);	
emit(doc["chipIds"], doc);	
}
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
}
};

var allAssetsView = database.GetView(Constants.AllAssetsView);
allAssetsView.SetMap(assetViewFilterMapDelegate, "1");
}

with following query mechanism -

{
                    var assetsDocsView = database.GetView(viewName);
                    var query = assetsDocsView.CreateQuery();

                    query.StartKey = key;
                    query.EndKey = key;

                    if (startPosition >= 0)
                    {
                        query.Skip = startPosition;
                    }

                    query.Limit = recordCount > 0 ? recordCount : query.Limit;

                    var rows = query.Run();

                    objRetValue = rows.Select(result => JsonConvert.DeserializeObject<T>(JsonConvert.SerializeObject(result.Value, Formatting.Indented))).ToList();
                }

Let me know if I’m missing anything.

6

(FYI, you should put a line of three back-quotes before and after code listings, so they get formatted as code. I’ve edited your post to use those, so I could read it more easily.)

That code looks OK to me, aside from the Select expression (I don’t know .NET.) Basically it’s going to return any asset document whose assetNumber, serialNumber, assetBarcode or chipIDs is equal to the requested key.

@borrrden, any opinion on the .NET stuff in the code?

7

Nothing looks wrong to me either, but the last line is a bit odd (serializing and then deserializing an object? Is that to get rid of the intermediate JSON .NET LINQ classes?).

By the way because you have changed the body of the map function, you need to also change its version or you won’t get new results.

8

Yes, we do change the Map version whenever there’s Map code changed.
The search here finds only the rows who’s keys are exactly matched. Is there any way to perform ‘Contains’ search for the document properties?
I’ve to look through almost 10 different properties for search token to find the documents.

9

Is there any way to perform ‘Contains’ search for the document properties?

There’s a full-text search capability in the native iOS/Mac CBL, but not in .NET. (Also, in the upcoming 2.0 version, all platforms will support full-text search.)

A way to get FTS-like capabilities is to have the map function break each string into words and emit each unique word separately as a key. Then you can query with any individual word as the key and find all the documents that contain it.

10

I assume the Full Text Search will do the search in all the fields. That is something we don’t want to do. Value supplied must be compared to the specifici field of the document.

For N1QL indexes with suffixes helps a lot for LIKE queries, we were hoping we can do something similar when indexing on VIEW.

11

Even if I break the existing text into words and emit, I’ll not be able to perform partial text search as rows are found only for exact key match.

Is there any way to perform partial string search in document properties?

e.g

Following document should be found if I specify the search token as ‘274’, having emitted the assetBarcode, assetNumber, etc fields (assetBarcode contains the search token 274).

{
"_id": “asset::021d7aa1-8d1c-4a04-8d25-c09f4a4ab4fc”,
"_rev": “4-20f98b257c66403033b9891af66cd36f”,
“assetBarcode”: “798274238”,
“assetDescription”: “Jet MM-0300 3T Lever Tool”,
“assetDiagram”: null,
“assetGPSLat”: null,
“assetGPSLong”: null,
“assetImage”: “080b6c60-b4ac-4b64-b20e-72ff3fc21c03”,
“assetLocation”: {
“id”: “location::54101::287308”,
“name”: “Site”,
“parentId”: “location::54101::78a93935-2124-4e51-9b21-f61c44fb75a0”
},
“assetNumber”: “AD-Asset#w76”,
“attributes”: [
{
“attributeId”: “attribute::3119”,
“attributeName”: “Hoist Type”
.
.
.
}

12

No, it indexes the string you tell it to index.

13

Yes. Query for all the other criteria, then iterate over each result row and do a string “contains” test on the relevant fields. (Which is what any query engine would do in this case.)

14

Thanks Jens. We currently are doing the same, post query filtering the records.