Re:Using PostgreSQL’s JSONB for NoSQL (2019) [video]
by bonzini on Sunday September 05, 2021 @08:21AM
(#28423784)
Yeah, I have a similar experience from a project that had moved from MongoDB to Postgres. In the conversion they left a free form JSON field with information filled in by plugins, which was messy because it was impossible to query efficiently. Plugins also had some code to format the free form data in HTML, which was suffering from code duplication but provided some ideas to build a common schema for a new table and for the corresponding REST API endpoints. That worked great but we still left a free form JSON field in the new table, and also kept the JSON field in the original table to store the configuration for the plugin.
In our case the "user" that just threw JSON data in and read it out was the plugins. You just have to strike the right balance between core parts of the schema, expressed as tables and relations, and auxiliary data that can use JSON.
However later I found that there two kinds of JSON: one “bad” that we hd to patch up with lots of validation to some spec that emerged, but another one that was absolutely right to be free form, because it was never accessed by us. The user simply inputs it freely, and reads it out somewhere else, with freedom to evolve it as desired.