How can we fetch documents with year wise in seperate bucket

Couchbase Server
1

I have a bucket called A in which i have all documents which includes a date in each document . I want to separate this documents into different bucket with yearly wise .

“sDate”: “2020-09-05 22:19:22” this is the parameter in which i have with different years in my documents .

Please help me what index required and which select query required to fetch documents on year wise.

2

Use the n1ql substr function to select on the year. String Functions | Couchbase Docs

Use INSERT INTO to insert into another bucket from a selection from one bucket.

INSERT INTO my_bucket (key _k, value _v) SELECT META().id _k, _v FROM travel-sample.inventory.route _v where _v.sourceairport=‘JFK’

Before executing the INSERT INTO, use Index Advisor for advice on indexing.

3

Thanks for your response.

4 
CREATE INDEX ix1 ON source(sDate);

INSERT INTO target(key _k, value _v) 
SELECT META().id _k, _v FROM  source AS s WHERE sDate LIKE "2020%";

OR

OR

CREATE INDEX ix1 ON default(SUBSTR(sDate,0,4) );

SELECT DISTINCT RAW SUBSTR(sDate,0,4)  
FROM default WHERE SUBSTR(sDate,0,4)  IS NOT NULL;

SELECT RAW SUBSTR(sDate,0,4)  
FROM default WHERE SUBSTR(sDate,0,4)  IS NOT NULL
GROUP BY SUBSTR(sDate,0,4);

The each value
SELECT RAW META().id  FROM  source AS s WHERE SUBSTR(sDate,0,4)  = $val;
Then use SDK get document insert into right collection

OR

CREATE INDEX ix1 ON default(SUBSTR(sDate,0,4) );

SELECT  META().id,  SUBSTR(sDate,0,4)  AS year
FROM default 
WHERE SUBSTR(sDate,0,4)  >= ""
ORDER BY SUBSTR(sDate,0,4);
You get document keys sorted by year. 
Get document from SDK, When year is different from previous year change your
 target collection and write there.
One pass all data copied
5

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.