3 ms·

What are the use cases for storing and manipulating json at the db level like this - why not use a relational schema and query it in the normal way?

• dystroy 3y ago
  Imagine you need to attach to some records a bag of data which you always fully need or not. This bag may have a tree structure, which is expensive to deal with in a relational model. An example would be the JSON configuration of a component/client/whatever. In such a case, trying to fit this bag of data in a relational model would be inefficient. Those bags of data are usually called "documents". And a lot of systems need a way to store them along relational data. If you're just storing and retrieving those documents, without any query, you don't need JSONB, a simple blob is enough. If you sometimes need to do some queries, especially free queries (you want all component whose configuration has some property), then JSONB is suitable as it lets you do the filtering in the database.
  • nojs 3y ago
    > If you sometimes need to do some queries, especially free queries (you want all component whose configuration has some property), then JSONB is suitable as it lets you do the filtering in the database. This feels like a slippery slope into a denormalised mess though. Before you know it your whole client record is a document and you’re using Postgres as a NoSQL database
    • jsmith45 3y ago
      The PostgreSQL's devs are fine with that. Such use cases may not be quite as optimized as relational usecases, but they should be possible. If you are doing that then perhaps a different database would be nicer or faster for that scenario, but again not PostgreSQL's concern. If the PostgreSQL devs were relational purists they would never have added special support for querying JSON. An application may be better served with a properly normalized schema, or it might not. That is a choice for the application developers to make. In practice competent developers should quickly realize if they went too nosql when a relational approach would provide benefits (Like if multiple "documents" need to reference consistent shared data, or if json query performance is not good enough) and normalize as needed to get those advantages. So long as the application does not contain random sql queries scattered everywhere (and doesnt treat its database as a sort of API for external access) then database refactoring is not impossible. Indeed the difficulty is often overestimated. It is seldom fun work, and tends to be a bit of a slog, and require more extensive testing before pushing to prod, but that happens.
• spappal 3y ago
  Align with the rest of the system, when sqlite is used as a component in a system which mainly speaks json. Performance, when records are complex and usually not accessed. Ease, avoiding/postponing table design decisions. Flexibility, for one of many corner-cases (since sqlite is a very broad tool). Incremental enhancement, e.g. when starting with sqlite as replacement to an ndjson-file and incrementally taking advantage of transactions and indexes on fields [1,2,3]. For example, several of these could apply when doing structured logging to a sqlite database. [1]: https://www.sqlite.org/expridx.html [2]: https://www.sqlite.org/gencol.html [3]: https://antonz.org/json-virtual-columns/ See also: https://www.sqlite.org/json1.html
  • mkl 3y ago
    Clickable: [1]: https://www.sqlite.org/expridx.html https://www.sqlite.org/expridx.html [2]: https://www.sqlite.org/gencol.html https://www.sqlite.org/gencol.html [3]: https://antonz.org/json-virtual-columns/ https://antonz.org/json-virtual-columns/ See also: https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html When you format links as code, they don't become links.
• ggregoire 3y ago
  There are good use cases, but from my personal experience, I've seen people use JSONB mainly to avoid to have too many columns when they inspect their tables, so they split their data into two groups, the ones supposedly important that have dedicated columns and the rest that is thrown into a single JSONB column.