51–60 of 210 posts
Earlier quoted context omitted.
Is there any downside to storing JSON-B even if you’re not planning to query it? For example, size on disk, read/write performance?
There’s processing to be done with JSONB on every read/write, which is wasted if you’re always reading/writing the full blob.
There's all the rest of open source, and then there's SQLite. A public domain software that doesn't accept contributions from outsiders, and that happens to run much of the world. And it just keeps getting better and better and better, and faster and faster and faster. I don't know how these guys manage to succeed where almost all other projects fail, but I hope they keep going.
It's perhaps the best example of the cathedral model of open source.
Just wondering how they will transition once the original few people at the top of the hierarchy need to retire, eventually it will happen.
I guess they need to find younger trusted committers with the same dedication and spirit. That's not necessarily easy. But for a piece of software as important as SQLite, I have a feeling they will find those.
Because it seems to not be common knowledge on this thread: JSONB is a format offered by Postgres for a while now, and is recommended over plain JSON primarily for improved read performance. https://www.postgresql.org/docs/current/datatype-json.html
Postgres's JSONB uses native numbers, which is faster but coerces values. It also removes duplicate keys but you shouldn't use those anyway. Sqlite's JSONB keeps numbers as strings, which is slower but preserves your weird JSON (since there's no such thing as standard JSON). I'm not sure about duplicate keys.
Earlier quoted context omitted.
If the order of items in the JSON blob matters then JSONB probably wouldn't preserve the order.
JSON is unordered. Nothing in your code should assume otherwise. "An object is an unordered collection of zero or more name/value pairs, where a name is a string and a value is a string, number, boolean, null, object, or array."
ON part of JSON doesn't know about objects. When serialized, object entries are just an array of key-value pairs with a weird syntax and a well-defined order. That's true for any serialization format actually.
It's the JS part of JSON that imposes non-duplicate keys with undefined order constraint.
You are the engineer, you can decide how you use your tools depending on your use case. Unless eg. you need interop with the rest of the world, it's your JSON, (mis)treat it to your heart's content.
Because it seems to not be common knowledge on this thread: JSONB is a format offered by Postgres for a while now, and is recommended over plain JSON primarily for improved read performance. https://www.postgresql.org/docs/current/datatype-json.html
Postgres's JSONB uses native numbers, which is faster but coerces values. It also removes duplicate keys but you shouldn't use those anyway. Sqlite's JSONB keeps numbers as strings, which is slower but preserves your weird JSON (since there's no such thing as standard JSON). I'm not sure about duplicate keys.
I-JSON is the most sensible JSON profile I know of: https://datatracker.ietf.org/doc/html/rfc7493. It says: UTF-8 only, prefer not to use numbers beyond IEEE 754-2008 binary64 precision, no duplicate keys, and a couple more things.
Despite internal format I see immediate external usage in applications. For example batch insertions in Python. Per row insert call has noticeable overhead. And JSONB could bring performance back with CTE: CREATE TABLE data(id, name, age); WITH ins AS ( SELECT c1.value, c2.value, c3.value FROM json_each('["some", "uuid", "key"]') c1 INNER JOIN json_each('["joe", "sam", "phil"]') c2 USING (id) INNER JOIN json_each('[1…
I typically do bulk inserts using a single JSON argument like this:
WITH ins AS (SELECT e.value ->> 'id', e.value ->> 'name', e.value ->> 'age' FROM json_each(?) e)
INSERT INTO data (id, name, age)
SELECT * FROM ins
There's all the rest of open source, and then there's SQLite. A public domain software that doesn't accept contributions from outsiders, and that happens to run much of the world. And it just keeps getting better and better and better, and faster and faster and faster. I don't know how these guys manage to succeed where almost all other projects fail, but I hope they keep going.
Earlier quoted context omitted.
It's perhaps the best example of the cathedral model of open source.
Yeah, learned about it while reading the documentation of Fossil (from the same SQLite people). Their approach certainly has its own merits (and drawbacks). Just wondering how they will transition once the original few people at the top of the hierarchy need to retire, eventually it will happen. I guess they need to find younger trusted committers with the same dedication and spirit. That's not necessarily easy. But…
I had always viewed this as a "future worry", and then Bram Moolenaar passed away :(
Earlier quoted context omitted.
JSON is unordered. Nothing in your code should assume otherwise. "An object is an unordered collection of zero or more name/value pairs, where a name is a string and a value is a string, number, boolean, null, object, or array."
You make it sound like it's one of the laws of physics. ON part of JSON doesn't know about objects. When serialized, object entries are just an array of key-value pairs with a weird syntax and a well-defined order. That's true for any serialization format actually. It's the JS part of JSON that imposes non-duplicate keys with undefined order constraint. You are the engineer, you can decide how you use your tools depe…
The text I quoted is from the RFC. json.org and ECMA-404 both agree. You are welcome to do whatever you want, but then it isn't JSON anymore.
Earlier quoted context omitted.
There’s processing to be done with JSONB on every read/write, which is wasted if you’re always reading/writing the full blob.
Which occurs with JSON as well (SQLite doesn't have a dedicated JSON nor JSONB type). The only actual cost would be the conversion between JSONB and JSON.