Reverse Regex / Pattern Matching

SQL++
1

Hi,

I have a requirement to implement a black list/ white list concept. We will be getting a user id from an API call and we need to lookup that userid pattern in documents and fetch matching documents.

e.g. user id received - 092712345

doc1{
code: A
uid: 09*
}

doc2{
code: B
uid: 0927*
}

doc2{
code: C
uid: 05*
}

In this situation, I want to be able to pull doc2 and return code as B as it matches the input userid most closely. There is no hard requirement to store uid as 09*. It can be whatever format (regex or anything else) that will fulfill the need.

Any suggestion on the best way to implement this?

2

Just an idea.

Query:

Select digit, code from pattern
where (digit = 9 and uid = '092712345') or 
... or 
 (digit = 2 and uid= '09') or
 (digit = 1 and uid= '0') order by digit Desc

Document (Legend):

<Document ID> : {
<JSON Contents>
}

Document Samples:

'2-09': {
code: 'A',
uid: '09',
digit: 2
}

'4-0927': {
code: 'B',
uid: '0927'
digit: 4
}

'2-05': {
code: 'C',
uid: '05',
digit: 2
}

You can utilize covering index to optimize the performance as I expect the size of the index would not have huge impact to the memory size in this case.

When you use query, Document ID can be anything while I assume that the above Document ID rule ensures no redundant pattern.
Or, the same operation could be logically realized with KV API by using the above Document ID examples (‘2-09’, ‘4-0927’…), although the programing could be a little bit complicated in comparison to the above query. The advantage is no dependency to query and index services.

There could be better ideas though, I hope this helps.

3

You could use (for regexp, the pattern would be e.g. “0927.*” for a “0927” prefix, of course):

SELECT ... FROM bucket b WHERE regexp_contains($uidparam,b.uid);

(ref: Pattern-Matching Functions | Couchbase Docs )

Or if you stored the filters as LIKE patterns (e.g. ‘0927%’):

SELECT ... FROM bucket b WHERE $uidparam LIKE b.uid;

(Ref: Comparison Operators | Couchbase Docs )

In either case it the server doesn’t actually care which element of the statement is the parameter and which is the document field - you specify them as suits your needs; the documents are returned if the condition returns true.

<edit>
These of course assume strings for the pattern document field and parameter value.
</edit>

HTH.