@orhan SEARCH can work without having to unnest a subobject. Sharing your document here would’ve helped, but assuming it’s of this format …
{
"id": "xyz",
"langDetails": [
{
"name": "one"
},
{
"name": "two"
}
],
"brandName": "brand",
"brandId": "1",
"isActive": true
}
And you define a default dynamic search index over it, here’s your query for it …
SELECT p.id, p.langDetails.name, p.brandName, p.brandId
from Products as p
where p.isActive=true AND SEARCH(langDetails.name, "one")
LIMIT 3
This is however not the most efficient query, my recommendation would be to define your search index well to make this a covered query where all operations are deferred to search. Here’s a definition you could try …
{
"type": "fulltext-index",
"name": "Products-idx",
"uuid": "",
"sourceType": "couchbase",
"sourceName": "Products",
"sourceUUID": "",
"planParams": {
"indexPartitions": 1
},
"params": {
"doc_config": {
"mode": "type_field",
"type_field": "type"
},
"mapping": {
"analysis": {},
"default_analyzer": "standard",
"default_mapping": {
"default_analyzer": "keyword",
"dynamic": false,
"enabled": true,
"properties": {
"brandId": {
"dynamic": false,
"enabled": true,
"fields": [{
"index": true,
"name": "brandId",
"store": true,
"type": "text"
}]
},
"brandName": {
"dynamic": false,
"enabled": true,
"fields": [{
"index": true,
"name": "brandName",
"store": true,
"type": "text"
}]
},
"id": {
"dynamic": false,
"enabled": true,
"fields": [{
"index": true,
"name": "id",
"store": true,
"type": "text"
}]
},
"isActive": {
"dynamic": false,
"enabled": true,
"fields": [{
"index": true,
"name": "isActive",
"type": "boolean"
}]
},
"langDetails": {
"dynamic": false,
"enabled": true,
"properties": {
"name": {
"dynamic": false,
"enabled": true,
"fields": [{
"index": true,
"name": "name",
"store": true,
"type": "text"
}]
}
}
}
}
}
},
"store": {
"indexType": "scorch"
}
},
"sourceParams": {}
}
And here’s the query for it …
SELECT smeta.fields.id, smeta.fields.`langDetails.name`, smeta.fields.brandName, smeta.fields.brandId
from Products as p
LET smeta=search_meta()
where SEARCH(p, {"query": {"conjuncts": [{"query": "langDetails.name:one"}, {"field": "isActive", "bool": true}]}, "fields": ["*"], "score": "none"})
LIMIT 3