How to write a view in CouchDB to represent "not in" or "group by having count(*) = 1"?

Question

Using relational db as an example, given two tables like below, where when rows in tableA and tableB have the same values, they represent the same "thing" but in different "state". So for ID=1, this thing has gone through stage1 and 2. But for ID=2, this thing has only gone through stage1.

tableA (Id, columnA, columnB)
         1, "a", "b"
         2, "x", "y"
         3, "z", "a"
tableB (Id, columnA, columnB)
         1, "e", "f"

I want to find all the rows from tableA that don't exist in tableB.

select  a.*
from    tableA a
  left join
        tableB b
  on    a.Id = b.Id
where   b.Id is null

So above SQL will show rows 2 and 3 from tableA.

How can I do similar things in CouchDB? Say I have 4 docs that look like below.

{ "_id":"a-1", "type":"A", "correlation_id": "1" }
{ "_id":"a-2", "type":"A", "correlation_id": "2" }
{ "_id":"a-3", "type":"A", "correlation_id": "3" }
{ "_id":"b-1", "type":"B", "correlation_id": "1" }

How can I create a view that only show docs id = a-2 and a-3? I don't want to filter, just want to show all docs that haven't got type B. I could kinda do a group by and count(*) equivalent with view, but I can't do a group by, having count(*) = 1 equivalent.

I'm using CouchDB 3.0.

Rie

I found some older posts. #14414453 But... the "list" function is no longer available. "List functions are deprecated in CouchDB 3.0, and will be removed in CouchDB 4.0." Perhaps I can't do "not in" using CouchDB and have to come up with a different document design. – Rie

are those docs the result of a view ? if they are just documents in the db your solution is a view. – Haehaecceity

Those "docs" with the "_id" properties above are CouchDB docs. I was able to do some "group by" using count and tiered keys for view, but I wasn't sure how to get "not in" behavior across 2 different types of docs. – Rie

Answer 1

you could write a view :

function (doc) {
  emit(doc.type, 1);
}

and then query the view using the key "A" and include_docs=true if you wanted the whole content of those documents.

If you not only want "A" but "everything but B" you can query from start to B and then from B to end and get all the documents that way.

Depending on your setup it might be easier to query the view with group_level=1 so you get all the keys and then loop through them excluding B to get the rest of the info you're interested in.

Haehaecceity

Could you elaborate on " query from start to B and then from B to end and get all the documents that way"? I want to get these docs. { "_id":"a-2", "type":"A", "correlation_id": "2" } { "_id":"a-3", "type":"A", "correlation_id": "3" } – Rie

Please elaborate on "query the view with group_level=1 so you get all the keys and then loop through them excluding B to get the rest of the info you're interested in". I did try to use group_level=1 but maybe I didn't know what I was doing. – Rie

querying a view is really the basic block for couchdb. It's all described here docs.couchdb.org/en/stable/api/ddoc/views.html – Haehaecceity

If you want to use group_level=XX, your key needs to be an array to match the array you would be emitting in your view. So if you emit([doc.type,doc._id],1) you can then use group_level=1 when you query that view and this would return all the unique doc.type – Haehaecceity