What’s the difference between JSONB and BSON?
JSONB has landed
191–200 of 210 posts
Re: JSONB has landed
#192So what will JSONB look like in a standalone DB browser (DBeaver, etc.) ?
It's just a BLOB.
Re: JSONB has landed
#193Earlier quoted context omitted.
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 system…
> 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
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.
Re: JSONB has landed
#194"The central idea behind this JSONB specification is that each element begins with a header that includes the size and type of that element."
Why not add this size indication to JSON specification. Would reduce memory requirements for JSON processing.
1997: https://cr.yp.to/proto/netstrings.txt
NB. This header may be the "central idea" behind JSONB but JSONB has other differences from JSON. This comment refers only to the size indication not the other features.
Re: JSONB has landed
#195Re: JSONB has landed
#196Earlier quoted context omitted.
JSON processors are not required nor expected to retain object key ordering from either input or object construction order.
The question is whether people think of their database as a processor of data or a storage location for data, exactly as it was provided. Given that a lot of people use SQLite as the latter, it's a worthwhile caveat to make people aware of. Additionally, Javascript environments (formalized in ES2015), your text editor, and your filesystem all guarantee that they'll preserve object key order when JSON is evaluated. It…
> The question is whether people think of their database as a processor of data or a storage location for data
If you use the database's JSON type(s) and/or functions, then yes, your database is a JSON processor.
And, yes, your database is not a dumb store, not if it's an RDBMS. The whole point of it is that it's not a dumb store.
Re: JSONB has landed
#197Earlier quoted context omitted.
> Yes, but it's not impossible to achieve it in practice. For any one implementation. But there's a very large number of implementations. You just can't count on object key order being preserved, so don't.
There are situations where keeping order is useful. For example, human-editable json config files. I also gave an example of two implementations that are compatible for a useful subset of keys. By the way, SQLite JSONB keeps object keys in insertion order, similar to Python: https://news.ycombinator.com/item?id=38547254
> There are situations where keeping order is useful. For example, human-editable json config files.
You might have to just normalize every time you want to edit that.
Re: JSONB has landed
#198Earlier quoted context omitted.
> There is no json data type Why not? I feel like a database should allow for a JSON data type that stores only valid JSON or throws an exception. It would also be nice to be able to access subfields of the JSON. SELECT userid, username, some_json_field["some_subfield"][0] FROM users where ... Not sure where to give feature suggestions so I'm just leaving this here for future devs to find
> It would also be nice to be able to access subfields of the JSON. Not that it's not _useful_ sometimes, but it amuses me that this is a huge violation of 1NF and people are often ok with it. It really depends on whether you're treating the JSON object as an atomic unit on its own, regardless of contents, or using the JSON to store more fine-grained information. I guess the same argument can be made for XML data typ…
Database design is, unfortunately, a lost art. Up through the early 2010's I remember having design reviews for database schemas, etc. That isn't "agile"... so you just fix it in the next sprint, as you explain to someone what a unique constraint is and why a table is now full of duplicates.
Re: JSONB has landed
#199Hm I googled and found this draft of the encoding - https://sqlite.org/draft/jsonb.html It feels like it would be better to use a known binary encoding. I thought the MessagePack data model corresponded pretty much exactly to JSON ? Edit: someone else mentioned BSON - https://bsonspec.org/ To be honest the wins (in this draft) don't seem that compelling The advantage of JSONB over ordinary text RFC 8259 JSON is that…
CBOR should compress even better than BSON.
Re: JSONB has landed
#200Earlier quoted context omitted.
> There is no json data type Why not? I feel like a database should allow for a JSON data type that stores only valid JSON or throws an exception. It would also be nice to be able to access subfields of the JSON. SELECT userid, username, some_json_field["some_subfield"][0] FROM users where ... Not sure where to give feature suggestions so I'm just leaving this here for future devs to find
> It would also be nice to be able to access subfields of the JSON. Not that it's not _useful_ sometimes, but it amuses me that this is a huge violation of 1NF and people are often ok with it. It really depends on whether you're treating the JSON object as an atomic unit on its own, regardless of contents, or using the JSON to store more fine-grained information. I guess the same argument can be made for XML data typ…
1NF is a theory construct that makes no accommodation for real-world performance. I'm shuddering even thinking how many tables and joins I would need to store some of these 3rd party JSON things I need to import & refine.