0

We have a .NET Core application that works as a datawarehouse and we store our entities in json documents that we regroup by projects. Usually projects would easily have 300k json documents with 50 to 250 fields. These fields are unstructured because the documents come from several sources selected by the user and that can have custom data.

When a user wants to query something, we go through all the json documents to perform the query (a regular table scan). We have some views that take 15min to be displayed because of the multiple queries that it contains ... This is a very bad approach but it works, so the clients are still "happy" with it because they don't have other possibilities.

With a WebApp we would have used PGSQL but the main constraint made by our clients is that the app has to open self-contained databases (like .db files for SQLite).

So in order to improve this I have several questions:

  • Is there anyway way to index all the fields of JSON documents using existing .NET libraries? So that we would perform the queries on this index before loading the required documents.
  • If not, I'm thinking of creating a cache for each view to contains the results of the queries so that only modified documents will be checked again when there is an update.
3
  • What about using a standard indexing library like Lucene.Net? I never tried that by myself, but it looks promising.
    – Doc Brown
    Feb 12, 2021 at 11:36
  • ..., it would be really nice if you could tell us if you had a look at Lucene.Net and tell us if it may be a solution for your problem. If you are looking for something different, that would others save the time to write answers which don't fit.
    – Doc Brown
    Feb 12, 2021 at 18:32
  • Sorry for the late answer, I took time to check Lucene.NET and it seems overkill for what I'm trying to do. I think that there is no lite solution to do such things (index all fields of multiple json documents) and I'm not even sure this is something that should be done. I'll probably just work on some view caching that would be updated after each push of new or updated json documents.
    – mwryl
    Feb 18, 2021 at 10:44

1 Answer 1

1

I think that caching the views until a change happens is a perfectly viable approach, so long as updates are rare enough or if it is acceptable to group a larger amount of changes for a single re-calculation of the views.

But you can easily mimick an index as a separate table, though at the cost of a lot of storage space. I haven't tried but a table like this would make sense to me:

document_id BIGINT NOT NULL,
property_name VARCHAR NOT NULL,
value TEXT NOT NULL # json of the value,
PRIMARY KEY (document_id, property_name)
# plus an index on property_name

And then just fill it when new documents come in / existing ones change.

Also, if i understand postgres jsonb correctly, you can try to implement that yourself. You'll still have to load the entire cell value, but at least you can skip on a lot of json parsing for each row.

1
  • Thank you very much for your answer. Yes caching the views seems to be the only real solution to handle this. The table you're suggesting to index the fields works like an Entity Attribute Value model which perform very badly when it comes to bulk insert of thousands of json documents. 😬
    – mwryl
    Feb 18, 2021 at 10:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.