[vec]

Be very careful with this logic. I'm in the middle of cleaning up a project where the original developer did this calculus and stored a lot of metadata as JSON strings in text fields. Now, three years out, we have new reporting requirements that need to be able to filter on some of that metadata.

Experience has taught me that it's next to impossible to know what types of queries will be needed for the entire lifetime of a project. If you absolutely must store data like this, please leave a clear migration path for moving some or all of it into well structured tables when (not if) it becomes necessary.

[garethrees]

I understand your concern. Perhaps if I give an example, it will be clear why I followed the approach I did.

The project is an industrial control system. Different kinds of item are processed by the system. Each kind of item needs image acquisition parameters, for example field of view. There's no use case that needs to select kinds of item whose image capture parameters specify a particular field of view, and it's clear that such a use case is very unlikely to arise — the image acquisition parameters are part of the internal specification of the system, not something that any end user is interested in.

At database design time it wasn't settled which model of camera was going to be used, and so it was far from settled which parameters were going to be needed. Using a JSONB field for the image acquisition parameters meant that I could leave the specification of these parameters to the camera programmer, without having to incur a series of database migrations as the correct set of parameters were worked out.

Even in your project, where new reporting requirements eventually arose, it doesn't mean the original decision was necessarily a bad one. Sometimes it makes sense to incur technical debt in order to bring a product to market in a timely fashion.