Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

JSONB has landed

sqlite.org

Post reply on HN
Print view View original

101–110 of 210 posts

Re: JSONB has landed

#101

Post by spappal »

nojs wrote: post #86

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?

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

Re: JSONB has landed

#102

Post by nojs »

dystroy wrote: post #98
nojs wrote: post #86

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?

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

Re: JSONB has landed

#103

Post by OJFord »

Anyone know what their release process is like, will this be in v3.45? (Downloads page has latest release as 3.44, and TFA says this is in pre-release snapshot.)

I don't use SQLite directly much, but I'd be keen to use this in Cloudflare's D1 & Fly.io. Having said that though, I'm not sure they publicise sqlite version (or even that it isn't customised) - double-checking now they currently only talk about importing SQLite3 dumps or compatible .sql files, not actually that it is SQLite.

So API changes like this actually break that promise don't they? Even though you wouldn't normally think of an addition (of `jsonb_*` functions) as being a major change (and I know it isn't semver anyway) they are for Cloudflare's promise of being able to import SQLite-compatible dumps/query files, which wouldn't previously have but henceforth might contain those functions.

Re: JSONB has landed

#105

Post by mike_hock »

cdogl wrote: post #46
mike_d wrote: post #38

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."

The same goes for maps in Go, which now explicitly randomizes map iteration with the range keyword to prevent developers from relying on a particular ordering. Neat trick.

That might get them to rely on the randomization, though :)

Re: JSONB has landed

#106

Post by mkl »

spappal wrote: post #101
nojs wrote: post #86

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?

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 advan…

Clickable:

[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

When you format links as code, they don't become links.

Re: JSONB has landed

#107

Post by cztomsik »

dspillett wrote: post #93
cztomsik wrote: post #87

Earlier quoted context omitted.

I don't know what SQLite does, but in JS the order is actually defined. JS is not Java, object is not HashMap, if anything, it's closer to LinkedHashMap, but even that is not correct because there are numeric slots which always go first. https://tc39.es/ecma262/#sec-ordinaryownpropertykeys

> but in JS the order is actually defined Defined yes, but still arbitrary and may not be consistent between JSON values of the same schema, as per the document you linked to: > in ascending chronological order of property creation Also, while JSON came from JS sort-of, it is, for better or worse (better than XML!) a standard apart from JS with its own definitions and used in many other contexts. JSON as specified do…

Yes, I know, the point was not to say that it's safe to depend on this universally, but rather why it's safe in JS and why it's not elsewhere -> other languages use hash maps simply because authors were either lazy or unaware of the original behaviour. (which sucks, in my opinion, but nobody can fix it now)

Re: JSONB has landed

#108

Post by 6510 »

vsnf wrote: post #70
LVB wrote: post #41

Earlier quoted context omitted.

The patterns I've noticed, watching it evolve for nearly 20 years are: - They draft behind others, and I mean this in a good way. They seem to eschew trailblazing new features, but keep a close eye on alternatives (esp. Postgres) and how use cases are emerging. When a sufficiently interesting concept has stabilized, they bring it to SQLite. - Closely related to this, they seem to stay clear of technical and community…

When put this way, it reminds me how Apple generally chooses to add features to the iPhone. They wait for use cases to be proven, usually by Samsung, and then add a very polished version to the phone.

Arguably the entire app eco system exists only to farm features. This is arguably the case whenever users are allowed to create things.

Go get a pro subscription with each of your competitors :)

Re: JSONB has landed

#109

Post by sltkr »

plq wrote: post #54
mike_d wrote: post #38

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…

> It's the JS part of JSON that imposes non-duplicate keys with undefined order constraint.

Actually since ES2015 the iteration order of object properties is fully defined: first integer keys, then string keys in insertion order, finally symbols in insertion order. (Of course, symbols cannot be represented in JSON.)

And duplicate properties in JSON are guaranteed to be treated the same way they are in object literals: a duplicate overwrites the previous value but doesn't change the order of the key.

Concretely that means if you write:

    Object.entries(JSON.parse('{"a":10, "1":20, "b":30, "a":40}'))
This is guaranteed to evaluate to:

    [['1', 20], ['a', 40], ['b', 30]]
(Note that '1' was moved to front, and 'a' comes before 'b' even though the associated value comes from the final entry in the JSON code.)

Python made a similar change in version 3.6 (officially since 3.7), both with regards to insertion order and later values overwriting earlier ones while preserving order. I think the only difference at this point is that Python doesn't move integer-like keys to the front, because unlike JavaScript, Python properly distinguishes between different key types.

Re: JSONB has landed

#110

Post by debugnik »

zmmmmm wrote: post #90
luhn wrote: post #23

Lots of confusion on what JSONB is. To your application, using JSONB looks very similar to the JSON datatype. You still read and write JSON strings—Your application will never see the raw JSONB content. The same SQL functions are available, with a different prefix (jsonb_). Very little changes from the application's view. The difference is that the JSON datatype is stored to disk as JSON, whereas the JSONB is stored…

I don't know if it's true of SQLite, but you missed the most important point at least with PostgreSQL : you can build indexes directly against attributes inside JSONB which really turns it into a true NoSQL / relational hybrid that can let you have your cake and eat it too for some design problems that don't fit neatly into either pure relational or pure NoSQL approaches.

You can do this with text JSON in SQLite as well, but JSONB could speed it up.
Post reply on HN