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

61–70 of 210 posts

Re: JSONB has landed

#61

Post by radarsat1 »

I have some cases where sometimes I want to store a vector of floats along with a data item. I usually don't need to match against the column, just store it. I know I could use BLOB but often I just use JSON for this kind of thing so that I don't have to deal with data types and conversion. It's wasteful and imprecise though due to the string conversion. Is JSONB a good middle ground option for these cases?

Edit: Sorry, just saw the comment below by o11c,

> Sqlite's JSONB keeps numbers as strings

which means the answer to my question is basically "no".

Re: JSONB has landed

#62

Post by bvrmn »

matharmin wrote: post #56
bvrmn wrote: post #44

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…

You don't need JSONB for this - doing this with plain JSON is simpler and already faster than individual inserts for most bindings in my experience. 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 The same approach can be used for bulk updates and dele…

I have quite wide records (over 50 fields) and ->> performs not well with text keys. I did not try it with array indexing though:

    WITH ins AS (
        SELECT value ->> 0, value ->> 1, value ->> 2
        FROM json_each('[["some", "joe", 10], ["uuid", "sam", 20], ["key", "phil", 30]]')
    )
    INSERT INTO data (id, name, value)
    SELECT * FROM ins

Re: JSONB has landed

#63

Post by lifthrasiir »

oefrha wrote: post #60
lifthrasiir wrote: post #51

Earlier quoted context omitted.

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.

No, SQLite's "JSON" is just TEXT, there's no overhead with reading/writing a string.

That's what I said I think? "JSON" is a TEXT that is handled as a JSON string by `json_*` functions, while "JSONB" is a BLOB that is handled as an internal format by `jsonb_*` functions. You generally don't want JSONB in the application side though, so you do need a conversion for that.

Re: JSONB has landed

#64

Post by oefrha »

lifthrasiir wrote: post #63
oefrha wrote: post #60

Earlier quoted context omitted.

No, SQLite's "JSON" is just TEXT, there's no overhead with reading/writing a string.

That's what I said I think? "JSON" is a TEXT that is handled as a JSON string by `json_*` functions, while "JSONB" is a BLOB that is handled as an internal format by `jsonb_*` functions. You generally don't want JSONB in the application side though, so you do need a conversion for that.

There’s no point using json_* functions if you’re always reading/writing the full blob.

Re: JSONB has landed

#65

Post by BozeWolf »

p1necone wrote: post #35
breadchris wrote: post #29

I didnt understand the purposes of document stores until the past couple of years and they are fabulous for building POCs. Enhanced JSON support will help a lot for making sqlite a suitable document store. I get full type support by serializing and deserializing protobuf messages from a db column and not making this column JSONB means i can filter this column too, instead of having to flatten the searchable data to o…

Yeah as long as you're reading and writing to the database with the same language, and that language has good type safety the benefits of your database schema effectively being defined by the same types as the rest of your code is pretty nice for a lot of use cases. You just have to be vigilant about correctly migrating existing data to the current shape if you ever make breaking changes to types.

This. Would be nice if there was a framework (in go, or python pydantic) which would help me migrate data made with old structs to new structs. And also deal with the transaction.

For now i use sqlite to deal with transactions and only make backward compatible updates to structs. Brittle, but it is a toy app anyways.

(Normally use django to deal with models and migrations, but wanted to do something different)

Re: JSONB has landed

#66

Post by lifthrasiir »

oefrha wrote: post #64
lifthrasiir wrote: post #63

Earlier quoted context omitted.

That's what I said I think? "JSON" is a TEXT that is handled as a JSON string by `json_*` functions, while "JSONB" is a BLOB that is handled as an internal format by `jsonb_*` functions. You generally don't want JSONB in the application side though, so you do need a conversion for that.

There’s no point using json_* functions if you’re always reading/writing the full blob.

Of course (and I never said that), but if you need to store a JSON value in the DB and use a JSONB-encoded BLOB as an optimization, you eventually read it back to a textual JSON. It's just like having a UNIX timestamp in your DB and converting back to a parsed date and time for application uses, except that applications may handle a UNIX timestamp directly and can't handle JSONB at all.

Re: JSONB has landed

#68

Post by akira2501 »

lifthrasiir wrote: post #63
oefrha wrote: post #60

Earlier quoted context omitted.

No, SQLite's "JSON" is just TEXT, there's no overhead with reading/writing a string.

That's what I said I think? "JSON" is a TEXT that is handled as a JSON string by `json_*` functions, while "JSONB" is a BLOB that is handled as an internal format by `jsonb_*` functions. You generally don't want JSONB in the application side though, so you do need a conversion for that.

Yes, but when you use the BLOB with jsonb functions, your application demands go from:

Read JSON from TEXT column.

Parse JSON into Internal Binary Format.

Run json_*() function on this format, which will Serialize Internal Binary Format to JSON as output.

To:

Read JSONB from BLOB column.

Run json_*() function on Internal Binary Format, which will serialize the Internal Binary Format to JSON as output.

Because:

The json_* and jsonb_* all accept _either_ JSON or JSONB as their input. The difference is jsonb_* functions also produces it as output. So even in the above case, if your function output is just being used to feed back into another table as a BLOB, then you can use the jsonb_* version of the function and skip the serialization step entirely.

Re: JSONB has landed

#69

Post by lifthrasiir »

akira2501 wrote: post #68
lifthrasiir wrote: post #63

Earlier quoted context omitted.

That's what I said I think? "JSON" is a TEXT that is handled as a JSON string by `json_*` functions, while "JSONB" is a BLOB that is handled as an internal format by `jsonb_*` functions. You generally don't want JSONB in the application side though, so you do need a conversion for that.

Yes, but when you use the BLOB with jsonb functions, your application demands go from: Read JSON from TEXT column. Parse JSON into Internal Binary Format. Run json_*() function on this format, which will Serialize Internal Binary Format to JSON as output. To: Read JSONB from BLOB column. Run json_*() function on Internal Binary Format, which will serialize the Internal Binary Format to JSON as output. Because: The js…

Oh, you are right! I later looked at the draft documentation and realized that `json(b)_` only determines the output type. That said, it is still true that you need one more function call `json(...)` to retrieve a textual form of JSON which you do often need instead of JSONB.

Re: JSONB has landed

#70

Post by vsnf »

LVB wrote: post #41
p-e-w wrote: post #8

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.

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.
Post reply on HN