fuzzy finding through database - prisma-CodePudding

I am trying to build a storage manager where users can store their lab samples/data. Unfortunately, this means that the tables will end up being quite dynamic, as each sample might have different data associated with it. I will still require users to define a schema, so I can display the data properly, however, I think this schema will have to be represented as a JSON field in the underlying database.

I was wondering, in Prisma, is there a way to fuzzy search through collections. Could I type something like help and then return all rows that match this expression ANYWHERE in their columns? (including the JSON fields). Could i do something like this at all with posgresql? Or with MongoDB?

thank you

CodePudding user response：

You can easily do that with jsonb in PostgreSQL.

If you have a table defined like

CREATE TABLE userdata (
   id bigint PRIMARY KEY,
   important_col1 text,
   important_col2 integer,
   other_cols jsonb
);

You can create an index like this

CREATE INDEX ON userdata USING gin (other_cols);

and search efficiently with

SELECT id FROM userdata WHERE other_cols @> '{"attribute": "value"}';

Here, @> is the JSON containment operator in PostgreSQL.

CodePudding user response：

Yes, in PostgreSQL you surely can do this. It's quite straightforward. Here is an example.
Let your table be called the_table aliased as tht. Cast an entire table row as text tht::text and use case insensitive regular expression match operator ~* to find rows that contain help in this text. You can use more elaborate and powerful regular expression for searching too.
Please note that since the ~* operator will defeat any index, this query will result in a sequential scan.

select * -- or whatever list of expressions you need
from the_table as tht
where tht::text ~* 'help';