Using Couchbase String functions in N1QL response

Question

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.]

vsr vsr · Accepted Answer · 2018-07-04T15:13:28

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

Using Couchbase String functions in N1QL response

1 Answers