CODEWITHSUNDEEP
couchbasesql++
k1eran
k1eran

Reputation: 4972

Using Couchbase String functions in N1QL response

I want to retrieve and manipulate some Couchbase documents, i.e. retrieve and do some string manipulation but I can't see a beginners' example online.

Am I using the right approach here or is there a easier way ...?

SELECT  meta().id    
FROM bucket_foo 
where meta().id like  "%-foobar"  
ORDER BY meta().id

This gives me query result ...

[
  {
    "id": "172.26.138.52-foobar"
  },
  {
    "id": "172.26.138.53-foobar"
  },
  {
    "id": "172.26.138.64-foobar"
  }
]

I want to remove the suffix i.e. convert it to something like :

 [
      {
        "id": "172.26.138.52"
      },
      {
        "id": "172.26.138.53"
      },
      {
        "id": "172.26.138.64"
      }
    ]

so after reading https://developer.couchbase.com/documentation/server/4.0/n1ql/n1ql-language-reference/stringfun.html I tried ...

SELECT  REPLACE(
  (SELECT meta().id  FROM foo where meta().id like  "%-foobar"  ORDER BY meta().id)
, "-foobar", "") as replace_all;

but I get 

[
  {
    "replace_all": null
  }
]

[I am a total Couchbase newbie.]

Upvotes: 3

Views: 1379

Answers (1)

vsr
vsr

Reputation: 7414

You are in right approach, subquery returns array and you can't replace on array. In this case subquery is not required.

You can use RTRIM() or REPLACE() or SUBSTR() or similar functions

SELECT RTRIM(meta().id,"-foobar") AS id
FROM bucket_foo
where meta().id like  "%-foobar"
ORDER BY id;

OR

SELECT REPLACE(meta().id,"-foobar","") AS id
FROM bucket_foo
where meta().id like  "%-foobar"
ORDER BY id;

OR

SELECT SUBSTR(meta().id,0,LENGTH(meta().id)-LENGTH("-foobar")) AS id
FROM bucket_foo
where meta().id like  "%-foobar"
ORDER BY id;

https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/stringfun.html

Upvotes: 3

Related Questions