Replace urls from "http://" to "https://" from all couchbase document

SQL++
1

In my couchbase bucket,some of the document contains “http://” urls are there .
I need a query to replace “http://” to “https://”
Example:
sample document,
{
name:“test”,
thumbUrl:“http://abc.com/test.png”
}

I want to replace above document content like,
{
name:“test”,
thumbUrl:“https://abc.com/test.png”
}

2

This query should take care of it:

update mybucket set thumbUrl = "https" || SUBSTR(thumbUrl, 4) 
where POSITION(thumbUrl, "http:") = 0
3

For updating single field this solution will work, but in my case I don’t know where url(http://) will come(might be from multiple properties).
I need to do a query for entire document.It should search “http://” and replace it with “https://”

4

If you want replace any where in the field you can use REPLACE(thumbUrl, “http:”,“https:”).
You will not be able to replace using N1QL without knowing the field name any where in the document.

If you want to know the document that has “http:” you can use following query, get the document using SDK’s and replace and update it back.

SELECT META(d).id 
FROM default As d 
WHERE ANY v WITHIN d SATISFIES CONTAINS(v,"http:") END;
1 Like
5

Can I get index for the above query ?
Because in my bucket I have almost “10 Lac” documents are there, so it is taking time to form result.

6

It is not easy to get the secondary index for this. You can try one of the following.

  1. Primary index
  2. CREATE INDEX ix1 ON default (DISTINCT ARRAY CONTAINS(v,“http:”) FOR v WITHIN self END)
7

could you please use replace in a full command (from beginning to end)? am having trouble implementing it. could you use replace on more than one field at a time?

8

You can replace more than one field, each must be separate SET clause in update.

UPDDATE default AS d 
SET d.a  = REPLACE(d.a, "http:","https:"), d.b = REPLACE(d.b, "http:","https:")
WHERE .........