N1QL insert-select

SQL++
1

Hi,
I am trying to do insert-select in N1QL. It is part of longer process…
I am trying to copy all docs of one type and modify them. I was thinking about saving them with a different key (and if possible type).

I have seen: Insert Data from another bucket using Query workbench

That example copies all values from one bucket to the other. How can I take just some cols? Can I also change the key of docs?

I was trying something like this:

INSERT INTO carly_test (key docId, value _v)
select site, max(docId) dealDocId, ‘uDeal’ docType, ‘uDeal::’ || site docId
from carly_test as _v
where docType like 'dealer%'
group by site

In insert-select example in your documentation it is confusing to me that you named have 3 things named _v: bucket alias, value parameter and ?. Is the 3rd thing (second col in select statement) a reference to all cols in bucket? Or some special function?

INSERT INTO bucket1 (key _k, value _v) SELECT META().ID _k, _v FROM bucket2 _v;

Regards,
Dejan

2

Hi @dejan, try this:
INSERT INTO tmp (key _k, value _v) SELECT META().id || “_new” _k, { “myid” : t.id, “myname” : t.name } _v
FROM travel-sample t WHERE type = “airline” limit 4 RETURNING meta(tmp).id, *;

In insert-select example in your documentation it is confusing to me

Thanks for the feedback. We will add more explanation. The 3 references to _v are correct and is referring to the documents of bucket_2

  1. bucket2 alias
  2. projection list of SELECT, which is all docs from bucket2, i.e (1)
  3. VALUE parameter, which is the output _v from SELECT in (2)

-Prasad

3

@prasad, just FYI, your back ticks need to be escaped here on forums.couchbase.com, as they have a formatting meaning here

4

thanks Matt.

Hi @dejan, you should escape the bucket name travel-sample here:

INSERT INTO tmp (key k, value _v) SELECT META().id || "new" _k, { "myid" : t.id, "myname" : t.name } _v 
FROM `travel-sample` t WHERE type = "airline" limit 4 RETURNING meta(tmp).id, *;
5

@ingenthr @prasad
is there any way to do insert and select if i have to do projectection of all the document of a particular type
SELECT META().id ||“_PT” _k,{s.*,“type” : “PT”} _v from system:completed_requests s limit 10
gives below error

[
{
“code”: 3000,
“msg”: “syntax error - at ",
“query_from_user”: "SELECT META().id ||"_PT" _k,{s.,"type" : "PT"} _v from system:completed_requests s limit 10”
}
]

6 
SELECT META().id ||"_PT" AS  _k,
OBJECT_PUT(s,"type" , "PT") AS _v
FROM system:completed_requests  AS s 
LIMIT 10;