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

201–210 of 210 posts

Re: JSONB has landed

#201

Post by yencabulator »

kevincox wrote: post #137
renegade-otter wrote: post #118

Earlier quoted context omitted.

Indeed. Those who use Postgres are already familiar with the difference. Rule of thumb: if you are not sure, or do not have time for the nuances, just use JSONB.

Slight caveat. It seems that you should default to JSONB for all cases except for values directly returned in the query result. As IIUC you will get a JSONB blob back and will be responsible for parsing it on the client. So use it for writes: UPDATE t SET col = jsonb_*(?) Also use it for filters if applicable (although this seems like a niche use case, I can't actually think of an example). But if returning values yo…

Wait is it really true that if you use SQLite's JSONB format, `select my_json_column from foo` becomes unreadable to humans? That seems.. unacceptable. One would expect it to convert its internal format back to JSON for consumption.

Re: JSONB has landed

#202

Post by ncruces »

mongol wrote: post #96

Unrelated question: what languages integrates best with sqlite? I am using it with go and cgo, but it is often advised to avoid cgo. Perhaps it doesn't matter so much, I can use it anyways, but would be interesting to hear about other experiences.

You have options that don't use cgo. Disclaimer, I made the second one.

https://modernc.org/sqlite https://github.com/ncruces/go-sqlite3

Re: JSONB has landed

#203

Post by mongol »

ncruces wrote: post #202
mongol wrote: post #96

Unrelated question: what languages integrates best with sqlite? I am using it with go and cgo, but it is often advised to avoid cgo. Perhaps it doesn't matter so much, I can use it anyways, but would be interesting to hear about other experiences.

You have options that don't use cgo. Disclaimer, I made the second one. https://modernc.org/sqlite https://github.com/ncruces/go-sqlite3

That's very cool, never considered a WASM option, thanks!

Re: JSONB has landed

#205

Post by ric2b »

pie_flavor wrote: post #177
sgbeal wrote: post #172

Earlier quoted context omitted.

> There’s a huge nuance worth mentioning: with JSONB you lose key ordering in objects. JSON never guarantees any ordering. From json.org: > An object is an unordered set of name/value pairs. If you depend on a given implementation doing so, you're depending on a quirk of that implementation, not on a JSON-defined behavior.

Using a function to query a fact about a string does not imply that the string should change, regardless of what guarantees are present within the format that the string is in. If you expect to get out the same string you put in, then there's nothing implementation-quirk about that.

You're not storing a string, you're storing a JSON object. If you want to store a string use TEXT or VARCHAR.

Re: JSONB has landed

#206

Post by gmfawcett »

ash wrote: post #167
btown wrote: post #163

Earlier quoted context omitted.

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…

> Javascript environments ... guarantee that they'll preserve object key order when JSON is evaluated Yes, but only for non-integer keys.

Is there such a thing as a non-string JSON key to be evaluated? https://www.json.org/ suggests not.

Re: JSONB has landed

#207

Post by yencabulator »

This does not look at all usable. Your application is exposed to the "internal binary format" that SQLite has chosen:

    select jsonb_extract('{"foo": {"bar": 42}}', '$.foo');

    ┌────────────────────────────────────────────────┐
    │ jsonb_extract('{"foo": {"bar": 42}}', '$.foo') │
    ├────────────────────────────────────────────────┤
    │ |7bar#42                                       │
    └────────────────────────────────────────────────┘

Re: JSONB has landed

#208

Post by pie_flavor »

ric2b wrote: post #205
pie_flavor wrote: post #177

Earlier quoted context omitted.

Using a function to query a fact about a string does not imply that the string should change, regardless of what guarantees are present within the format that the string is in. If you expect to get out the same string you put in, then there's nothing implementation-quirk about that.

You're not storing a string, you're storing a JSON object. If you want to store a string use TEXT or VARCHAR.

That is what this post chain is about. The nuance between choosing to store your JSON as a string in a TEXT column or as JSONB in a BLOB column.

Re: JSONB has landed

#209

Post by ash »

gmfawcett wrote: post #206
ash wrote: post #167

Earlier quoted context omitted.

> Javascript environments ... guarantee that they'll preserve object key order when JSON is evaluated Yes, but only for non-integer keys.

Is there such a thing as a non-string JSON key to be evaluated? https://www.json.org/ suggests not.

Yes, in the context of JSON handling by JavaScript. I meant string keys that can be interpreted as non-negative numbers. Those are traversed first, the rest of the keys are coming in insertion order. For example:

    > JSON.stringify({'one': 1, '3': 3, 'two': 2, '2': 2, 'three': 3, '1': 1})
    '{"1":1,"2":2,"3":3,"one":1,"two":2,"three":3}'

Re: JSONB has landed

#210

Post by gmfawcett »

ash wrote: post #209
gmfawcett wrote: post #206

Earlier quoted context omitted.

Is there such a thing as a non-string JSON key to be evaluated? https://www.json.org/ suggests not.

Yes, in the context of JSON handling by JavaScript. I meant string keys that can be interpreted as non-negative numbers. Those are traversed first, the rest of the keys are coming in insertion order. For example: > JSON.stringify({'one': 1, '3': 3, 'two': 2, '2': 2, 'three': 3, '1': 1}) '{"1":1,"2":2,"3":3,"one":1,"two":2,"three":3}'

Thanks for explaining. That behaviour is... interesting!
Post reply on HN