COUCHBASE SQL query not working

[jagadishnpy]

I am trying to select query and from the JSON for a particluar gid(09362219) and delete that particulat section alone . Please help me with a query so that i can select to see if that gid exists and delete the same.

“Id”: “UsrLstng::Sl”,
“UserList”: [{
“gid”: “71084554”,
“modules”: [
“Ord”,
“Geo”,
“Sal”,
“Unres”,
“data”
]
},
{
“gpid”: “09362219”,
“modules”: [
“Ord”,
“Geo”,
“Sal”,
“Unres”,
“data”
]
}]

[dh]

If you’re looking to just select the data removing the entry (i.e. not update the underlying document) you could:

SELECT d.*,ARRAY v FOR v IN d.UserList WHEN v.gid != "09362219" END UserList
FROM default d...

This is replacing the “UserList” returned by “d.*” with the array constructed in the projection aliased as “UserList”.

If you want to change the underlying document, then an UPDATE statement following similar lines will work:

UPDATE default d
SET UserList = ARRAY v FOR v IN d.UserList WHEN v.gid != "09362219" END
WHERE ...

Edit: If it doesn’t exist it doesn’t matter for the above statements. If you want to test for existence of such an element independently:

SELECT *
FROM default d
WHERE ANY v IN d.UserList SATISFIES v.gpid = "09362219" END
...

HTH.

[jagadishnpy]

@dh

SELECT *
FROM default d
WHERE ANY v IN d.UserList SATISFIES v.gpid = "09362219" END

I tried with a below one but it just running …Am i misisng someting ?Please let me know.

SELECT *
FROM bucketname d
WHERE ANY v IN d.UserList SATISFIES v.gid = “09362219” END

[dh]

How many documents are there in your keyspace? If there are a lot this will run for some time. Equally if the average length of the UserList array is large.

Perhaps further index-supported filters could be added to the statement so as to not be checking this array condition for every document in the keyspace?

You could also consider creating an index to support the statement, even if dropped after this activity:

CREATE INDEX ixt ON default(ALL ARRAY v.gpid FOR v IN UserList END)

But be warned this could be large (assuming a large number of documents / average UserList size) - index size could easily be an issue thanks to the array expansion. It may also take some time to create if on a large data set.

[jagadishnpy]

@dh

CREATE INDEX i

Ok . It returned the data in about 6 minutes but the whole data than for ‘09362219’.Below is the query .

SELECT *
FROM bucketname d
WHERE ANY v IN d.UserList SATISFIES v.gid = “09362219” END

[dh]

@jagadishnpy

but the whole data than for ‘09362219’

What are you looking for ? “*” is the whole document (or combination if multiple keyspaces are involved). If you’re looking for just the corresponding UserList element then you need to select that in your projection.

There are various ways to report just the elements in question:

(these are constructed to use the previously suggested index)

SELECT ARRAY v FOR v IN d.UserList WHEN v.gpid = "09362219" END
FROM default d
WHERE ANY v IN d.UserList SATISFIES v.gpid = "09362219" END

If you want just the first result not all possible UserList entries matching, then sub-script the ARRAY projection.

You could also UNNEST:

SELECT u.*                                                                                                                          
FROM default d
UNNEST ARRAY v FOR v IN d.UserList WHEN v.gpid = "09362219" END u
WHERE ANY v IN d.UserList SATISFIES v.gpid = "09362219" END

to get each matching array element as a separate result (the WHERE clause will use the index to pick documents; the ARRAY constructed for the UNNEST filters the elements - there are multiple ways you could do this).

HTH.