Insert-select settings and subquery

Been reviewing the docs but could use a hint.
In N1QL, trying to insert a new document, and most fields I will specify in the query sql.
But for one field, need to do a subquery with a where clause with a value I have, to get another fields value (and want to do it in one statement rather than do a select and bring this value back to the client for security reasons).
Example: trying to create a document with values (a,b,c,d)
But value ‘d’ needs to be a value from an existing record (select distinct somefield from x where d=‘1234’)
Can’t seem to figure out the N1QL syntax to do this programmatically…

INSERT INTO default VALUES ("key", {"a":1, "b":3, "c": "xyz", "d": (SELECT DISTINCT RAW f1 FROM bucket WHERE f2 = "10" LIMIT 1)[0] });


SELECT UUID() AS k , {"a":1, "b":3, "c": "xyz", "d":  f1} AS v
FROM bucket b WHERE  f2 = "10" ;
1 Like

those are great, thanks!
But one question, when I do this query:
select distinct raw something from x where xfield=‘jam’ and somefield2 =‘2222’ limit 1;
I still get an array of values back instead of just the single value stored in that field. Why?
“_sequence_result”: [

The result from a query is an array, even if there is only one record. Remember that Couchbase returns the entire result set always in one go - it isn’t like, for instance, a typical RDBMS cursor where you have to retrieve each array element one at a time.

1 Like

@dh already explained. That is reason in first INSERT around subquery (including inside LIMIT 1) has [0] pick the first element from the array.

1 Like