Creating a web application with full text search on dynamic data

Question

Even after thorough requirements engineering we end up with users wanting to attach 'notes' to their otherwise well-structured data records, in other words: arbitrary key-value pairs. Their primary interest is to find records later based on this meta data.

Example:

{
 fixedattr1: 10,
 fixedattr2: 11,
 fixedattr3: 12,
 foo: 'bar',
 lol: 2
}

and

{
 fixedattr1: 0,
 fixedattr2: 1,
 fixedattr3: 2,
 baz: 'rofl'
}

could be two such records. The primary interest is to be able to

1. add new key-value pairs on the fly
2. find records having certain key-value pairs

Obviously this is not going to fly with relational databases. Mongodb (for instance) can handly dynamic attributes (by storing them in the same document), and in general the MEAN stack could be used to build a web app.

Let's accept this idea for a moment, then the question is: what level of support is there in MEAN to index the documents based on the dynamic attributes? Or is there another technology platform that handles this?

Answer 1

PostgreSQL supports both JSON data type and GIN indexes out of the box so that will fly just fine with relational databases. You can also choose to model some of the fixed fields as normal columns and only use JSON for the dynamic fields. I would favor PostgreSQL over MongoDB especially if you also need proper ACID transactions and if your data fits on one node.

Answer 2

As far as I am concered. Mongo could index a document, but I am not sure if that index goes for "abitrary" records. One technology that comes to mind if we talk json documents would be elasticsearch. That was build for ... searching.