Assign incrementing number to each document in N1QL

SQL++
1

I’m trying to assign an incrementing number to a series of documents. Feels like it should be simple, but I can’t figure out the syntax.

For example, I want to add an _id property to each document, such that the ids increment:

{"_id": 1, "type": "record"}, {"_id":" 2, "type": "record"}, etc.

Tried using a correlated subquery to get the next id, but it always returns the same number, instead of an incrementing number.

Suggestions? Thanks in advance.

2

This is probably not the most efficient, but you can try:

UPDATE default d SET _id = (SELECT RAW x.num FROM (SELECT ROW_NUMBER() OVER() AS num, meta(d1).id AS docId FROM default d1) x WHERE x.docId = meta(d).id)[0];

You can modify the innermost subquery to order the documents the way you like.
Requires Couchbase Server version 6.5 or above.

1 Like
3

Another variation,

MERGE INTO default AS m USING (SELECT META(t).id, ROW_NUMBER() OVER() AS num FROM default AS t) AS s
ON KEY s.id
WHEN MATCHED THEN UPDATE SET m._id = s.num;
1 Like
4

Will give these a try - thanks for the input!