N1QL for array elements

Couchbase Server
, , ,
1

I have a json response stored to couchbase as document. I’m trying to retrieve the results by ‘businessDate’ and ‘buyerName’ using the index created as follow but unable to retrieve the results.

What is the correct way to perform N1QL query?

  1. CREATE INDEX idx on bucketName(businessDate,buyerName)
    My search queries:
  2. select * from bucketName where businessDate=‘2020-02-11’ and buyerName like ‘Lalan%’;
  3. select * from bucketName where buyerName = ‘Lalan Securities Private Limited’;

Secondly, I have to perform search using node.js, do I need to get the document first and then perform search on it or can I make a search within a document? My results are stored in one single array.

Please provide suggestions.

[{
	"bucketName": {
		"data": [{
			"businessDate": "2020-02-11",
			"buyerName": "Lalan Securities Private Limited",
			
		},
		{
			"businessDate": "2020-02-12",
			"buyerName": "Ankit Securities (PVT) Ltd.",
			
		}],
		"timestamp": 1581486674956
	}
}]
2

I have my answers now.
There are two methods to perform queries.

  1. Using Unnest, and
  2. Using Array

For the above data we can use the query:

select
   d 
from
   bucketName t unnest data as d 
where
   d.buyerName= "Lalan Securities Private Limited" 
   and d.businessDate=" 2020-02-11";

Option 2, using Array:

select
   ARRAY data FOR data in bucketName.data 
   when
      data.buyerName= "Lalan Securities Private Limited" 
      and data.businessDate= "2020-02-11"
      END
      as data 
from
   bucketName

In the above query, we are not first flattening the array through an UNNEST operation. Instead, we are using one of the collection operators to find array items that meet our criteria.

3 
CREATE INDEX ix1 ON  bucketName (DISTINCT ARRAY d.businessDate FOR d IN data END);


select
   ARRAY data FOR data in bucketName.data 
   when
      data.buyerName= "Lalan Securities Private Limited" 
      and data.businessDate= "2020-02-11"
      END
      as data 
from
   bucketName
WHERE ANY d IN data SATISFIES d.businessDate= "2020-02-11" AND d.buyerName= "Lalan Securities Private Limited" END;
1 Like
4

Is it really needed to put the search arguments twice, first with the WHEN clause and later with the SATISFIES clause? Will not this impact on performance?

Also, I’d be thankful to you if you would help me rewriting the CREATE INDEX query for two arguments. I’ll search based on “buyerName” and “businessDate”. Following query did not result but errored out “index not found.”

CREATE INDEX ix1 ON  bucketName (DISTINCT ARRAY {d.stockSymbol, d.businessDate} FOR d IN data END);
5

It needs both places. Projection expression starts with ARRAY and ends with END. Where caluse expression starts with ANY and end with END. You can’t use where clause derived expression in projection vice versa.

Whenever you reference data it comes from source i.e. bucket. If you don’t want that way use UNNEST.

The index will not qualify you check this out https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-arrays.html.

If you indexed {d.stockSymbol, d.businessDate} then UNNEST where clause or ANY … SATISFIES clause must have
{d.stockSymbol, d.businessDate} = {
“businessDate”: “2020-02-11”,
“buyerName”: “Lalan Securities Private Limited”}

This type of comparison is very hard always not possible. So index one search value and do indexscan and then apply post Fetch other predicates.

6

Great!
Thank you for the explanation in detail. I have results now.

CREATE INDEX `ix` ON `bucketName`((distinct (array {"businessDate": (`d`.`businessDate`), "stockSymbol": (`d`.`stockSymbol`)} for `d` in `data` end)))

Following query provided result:

    select timestamp,
       ARRAY data FOR data in test.data 
       when
          {data.stockSymbol,data.businessDate}={"stockSymbol":"ADBL","businessDate":"2020-02-11"}
          END
          as data 
    from
       bucketName
    WHERE
       ANY d IN data SATISFIES {d.stockSymbol,d.businessDate}={"stockSymbol":"ADBL","businessDate":"2020-02-11"}
       END;

I’m going to mark this as solved but opening a new question to implement the same query using node.js. Please let me know if you have any solution to it as well.

Thank you.
Ankit Lalan

1 Like
7

If you are looking equality only

CREATE INDEX `ix` ON `bucketName`((distinct (array  [d.stockSymbol , d.businessDate ] for `d` in `data` end)));
select timestamp,
       ARRAY data FOR data in test.data 
       when  dtata.stockSymbol = "ADBL" AND d.businessDate = "2020-02-11"  END
          AS  data 
    from
       bucketName
    WHERE
       ANY d IN data SATISFIES [d.stockSymbol,d.businessDate] = ["ADBL","2020-02-11"]  END;

Check this post for explanation Creating a covered index with array, how to access array elements in select?
Composite index from an array of objects

8

That works as well!
Below is the explanation of the search result.

{
	"plan": {
		"#operator": "Sequence",
		"~children": [{
			"#operator": "DistinctScan",
			"scan": {
				"#operator": "IndexScan3",
				"index": "ix1",
				"index_id": "73d87faba62f6479",
				"index_projection": {
					"primary_key": true
				},
				"keyspace": "bucketName",
				"namespace": "default",
				"spans": [{
					"exact": true,
					"range": [{
						"high": "[\"ADBL\", \"2020-02-11\"]",
						"inclusion": 3,
						"low": "[\"ADBL\", \"2020-02-11\"]"
					}]
				}],
				"using": "gsi"
			}
		},
		{
			"#operator": "Fetch",
			"keyspace": "bucketName",
			"namespace": "default"
		},
		{
			"#operator": "Parallel",
			"~child": {
				"#operator": "Sequence",
				"~children": [{
					"#operator": "Filter",
					"condition": "any `d` in (`bucketName`.`data`) satisfies ([(`d`.`stockSymbol`), (`d`.`businessDate`)] = [\"ADBL\", \"2020-02-11\"]) end"
				},
				{
					"#operator": "InitialProject",
					"result_terms": [{
						"expr": "(`bucketName`.`timestamp`)"
					},
					{
						"as": "data",
						"expr": "array `data` for `data` in (`bucketName`.`data`) when (((`data`.`stockSymbol`) = \"ADBL\") and ((`data`.`businessDate`) = \"2020-02-11\")) end"
					}]
				},
				{
					"#operator": "FinalProject"
				}]
			}
		}]
	},
	"text": "select\n   timestamp,\n   ARRAY data FOR data in bucketName.data \n   when\n      data.stockSymbol = \"ADBL\" \n      AND data.businessDate = \"2020-02-11\" \n      END\n      AS data \nfrom\n   bucketName \nWHERE\n   ANY d IN data SATISFIES [d.stockSymbol,d.businessDate] = [\"ADBL\",\"2020-02-11\"] \n   END;"
}
9

Hello,

When we use N1QL queries with multiple documents in one bucket, how does the search results out? Does it always look for the newly upsert document or results out by scanning all documents and providing relevant result?

I have two documents, my search query will result from both documents or from the last updated one? If latter, what happens with the old documents? How can I traverse to all documents?

image
image1190×369 41.1 KB

Query

select
   timestamp,
   ARRAY data FOR data in test.data 
   when
      data.stockSymbol = "ADBL" 
      AND data.businessDate = "2020-02-11" 
      END
      AS data 
from
   test 
WHERE
   ANY d IN data SATISFIES [d.stockSymbol,d.businessDate] = ["ADBL","2020-02-11"] 
   END;

Actually, I’m from RDBMS background so considering bucket as tables and Documents as rows. Also having a perception that for every subject area, I need to create buckets and upsert to particular bucket only. Am I correct?

10

From clause has bucket, Every document in the bucket is considered. If you don’t want you can specify where clause filter out.
Index is maintained asynchronously, few ms behind. If you need you can use scan_consistency. You should read the documentation.