Query to insert new document from an existing document through named parameter

SQL++
1

Hi All,

I am stuck to find the right solution of the below use case.

I have existing documents in “travel-sample” bucket as below:

{
“travel-sample”: {
“id”:1001,
“travelid”: “travel_1001”,
“callsign”: “MILE-AIR”,
“country”: “United States”,
“iata”: “Q5”,
“icao”: “MLA”,
“name”: “40-Mile Air”,
“type”: “airline”
}
},
{
“travel-sample”: {
“id”:1002,
“travelid”: “travel_1003”,
“callsign”: “TXW”,
“country”: “United States”,
“iata”: “TQ”,
“icao”: “TXW”,
“name”: “Texas Wings”,
“type”: “airline”
}
}.
{
“travel-sample”: {
“id”:1003,
“travelid”: “travel_1003”,
“callsign”: “TXW”,
“country”: “United States”,
“iata”: “TQ”,
“icao”: “TXW”,
“name”: “Texas Wings”,
“type”: “airline”
}
}

Value of travelid is derived by prefixing with static text “travel_” to the value of id. As shown in the above example, if value of id is 1001, travelid will be travel_1001

The document key is on field travelid.

I want to insert a new field called “p_id”. The value of “p_id” will populated through named parameter (as suggested in Update query to add new attribute and its value in existing document).

\set -$inputcsv {“1001”:“2121”, “1002”:“2222”, “1003”:“2323”} ;

Additionally, I would need to update the value of travelid as travel_<p_id>, e.g. travel_2121. But, as this is the key field, though the value can be updated, the document key still retains the old value.

One of the solution I can think of is to create a new document with the new value of travelid as document key, i.e.

Desired Output

{
“travel-sample”: {
“id”:1001,
“p_id”:2121,
“travelid”: “travel_2121”,
“callsign”: “MILE-AIR”,
“country”: “United States”,
“iata”: “Q5”,
“icao”: “MLA”,
“name”: “40-Mile Air”,
“type”: “airline”
}
},
{
“travel-sample”: {
“id”:1002,
“p_id”:2222,
“travelid”: “travel_2222”,
“callsign”: “TXW”,
“country”: “United States”,
“iata”: “TQ”,
“icao”: “TXW”,
“name”: “Texas Wings”,
“type”: “airline”
}
}.
{
“travel-sample”: {
“id”:1003,
“p_id”:2323,
“travelid”: “travel_2323”,
“callsign”: “TXW”,
“country”: “United States”,
“iata”: “TQ”,
“icao”: “TXW”,
“name”: “Texas Wings”,
“type”: “airline”
}
}

Not able to figure out the right query to meet this requirement. Please help

Thank you,
Regards,
Jayant

2 
\set -$inputcsv {"1001":2121, "1002":2222, "1003":2323} ;

INSERT INTO `travel-sample`  VALUES( KEY v.trvaleid, VALUE v)
SELECT  OBJECT_CONCAT(t, {p_id, "travelid":"travel_"||TO_STRING(p_id)}) AS v 
FROM `travel-sample` AS t 
LET p_id = $inputcsv.[TO_STRING(t.id)]
WHERE TO_STRING(p_id) IS NOT NULL;

OR

INSERT INTO `travel-sample`  VALUES( KEY v.trvaleid, VALUE v)
SELECT  OBJECT_CONCAT(t, {p_id, "travelid":"travel_"||TO_STRING(p_id)}) AS v 
FROM `travel-sample` AS t 
LET p_id = $inputcsv.[TO_STRING(t.id)]
WHERE  TO_STRING(t.id) IN OBJECT_NAMES( $inputcsv) AND  TO_STRING(p_id) IS NOT NULL;

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/insert.html
https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/upsert.html

3

Thank you so much. I just tried with the first query and is working fine (after fixing minor syntactical error)

INSERT INTO travel-sample (KEY v.travelid, VALUE v) SELECT OBJECT_CONCAT(t, {p_id, ‘travelid’:‘travel_’||TO_STRING(p_id)}) AS v FROM travel-sample AS t LET p_id = $inputcsv.[TO_STRING(t.id)] WHERE TO_STRING(p_id) IS NOT NULL ;

Please help me with few more queries

  1. Please let me know what is the difference between the first and 2nd query
  2. In CBQ, shell, at a time how many pairs of named parameters we can set at the max?
  3. I have more than 10 million documents to create using such named parameter. Please suggest if we can choose to proceed with the same approach or shall we explore the SDKs / REST API?

Thanks again for your help
Regards,
Jayant

4

If you are doing 10+Million documents that doesn’t work nor perform well. As you are doing on travel-sample not sharing actual document it is very difficult to advise.

Give me details:
Are u going to insert new documents in same bucket?
Are you need to delete old document?

Just give sample old, new document with keys and required fields. The one you referring travel sample keys and ids doesn’t match.

Example:
Old document key "travel_1003"     {"id": 1003, .......}
New document key "travel_2323"   {"id":1003, "p_id":2323,  “travelid”: “travel_2323”,   ......}
Input mapping {"old id": newid}  is new id is string or integer?
5

Hi,

Yes, we plan to insert new documents in the same bucket.
Deletion of old documents is something we would like to defer by 6 months.

Let me provide the sample old and new document with keys tomorrow.

6 
This approach I will follow:

CREATE INDEX ix1 ON default( META().id) WHERE  p_id IS MISSING;

Query :

SELECT RAW META(d).id 
FROM default AS d
WHERE  d.p_id IS MISSING AND META(p).id > $dockey LIMIT 10000;

Pick Any SDK:
          nextdockey  = ""
          Read the mapping into dictmap
          FOR loop for ever {
                     $dockey = nextdockey
                      Execute query with scan_consistency scan_plus
                      if query return no rows {
                                 break 
                      }
                      For each row  {
                                   nextdockey = value from the row
                                   do reactive API (may want to control number of concurrent reactive threads) {
                                         GET the document
                                         generate the new document and new document key from dictmap, old document
                                         INSERT the new document
                                  }
                       }
              }
7

Thanks for your input. Will analyze . As requested, attached are the sample documents.