CODEWITHSUNDEEP
node.jscouchbasesql++
Valery
Valery

Reputation: 11

first_error_message: 'syntax error - at ''

I'm trying to update an array inside a specific json document on a Couchbase Bucket. Basically what I want to do is to insert a new object to the array. When I try below query on the data Base itself, the query is working perfectly,

UPDATE BUCKETNAME SET appl[3]= {
      'appl_ads_id': 'testtttt',
      'appl_bnd_lvl_cd': 30,
      'appl_desire_skill_tx': [
        "couchbase",
        "nodejs"
      ],
      'appl_email_tx': "[email protected]",
      'appl_full_nm': "TEST TESTING",
      'appl_id': "4444",
      'appl_ldr_apprvd': true,
      'appl_locat_tx': "ALABAMA",
      'appl_role_nm': "Engineer III",
      'appl_status': "PENDING"}
          where opp_id= 1 and opp_nm= "Couchbase Management Project" RETURNING *;

but when i try and write that query into my javascript code Im getting below error:

QueryErrorContext {
     first_error_code: 3000,
     first_error_message: 'syntax error - at '',
     statement:
      'UPDATE `BUCKETNAME` SET appl[3]= {'appl_ads_id': 'albm111', 'appl_bnd_lvl_cd': 30, 'appl_desire_skill_tx': ['couchbase', 'nodejs'],'appl_email_tx': 'zzzzzzz','appl_full_nm': 'z Z','appl_id': '222222','appl_ldr_apprvd': false, 'appl_locat_tx': 'ALABAMA','appl_role_nm': 'Engineer III','appl_status': 'Test'} where opp_id= $ID and opp_nm= $NAME  RETURNING *;',
     client_context_id: '54f4f683f306b4a5',
     parameters: '',
     http_response_code: 400,
     http_response_body: '' } }

Please find below how I wrote the N1ql query on my nodejs code:

async function queryResults() {
  const query = "UPDATE BUCKETNAME SET appl[3]= {'appl_ads_id': 'mfern685', 'appl_bnd_lvl_cd': 30, 'appl_desire_skill_tx': ['couchbase', 'nodejs'],'appl_email_tx': 'zzzzzzz','appl_full_nm': 'z Z','appl_id': '222222','appl_ldr_apprvd': false, 'appl_locat_tx': 'Sunrise','appl_role_nm': 'Engineer III','appl_status': 'Test'} where opp_id= $ID and opp_nm= $NAME  RETURNING *;"
  
  
  const options = { parameters: { ID: 1, NAME: 'Couchbase Management Project' } }
  try {
    let results = await cluster.query(query,options);
    results.rows.forEach((row) => {
      console.log('Query row: ', row)
    })
    return results
  } catch (error) {
    console.error('Query failed: ', error)
  }
}
​

Upvotes: 1

Views: 210

Answers (1)

deniswsrosa
deniswsrosa

Reputation: 2460

The second part of your query should use standard SQL syntax:

update my_bucket set myArray[3] = {teste: 'something'}, appl_email_tx =  '[email protected]';

Upvotes: 1

Related Questions