How to search in unnested document array?

[orhan]

Hi,
How do I search in UNNEST document ARRAY.
What I’m doing wrong?

SELECT p.id ,langDetail.name ,p.brandName ,p.brandId
from Products AS p
UNNEST p.langDetails AS langDetail
WHERE p.isActive=true
AND SEARCH(langDetail.name,‘uzun kol’)
LIMIT 3;

Thanks.

[dh]

The first argument to SEARCH must be a keyspace - so it won’t work for an UNNESTed field.

( If all you’re looking for is a basic fixed string in the item, CONTAINS should suffice. )

SEARCH can of course be used to select the document based on the field - SEARCH(p.langDetails.name,'uzun kol') - but not limit the results to the matching element in the nested array.

HTH.

[abhinav]

@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