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

181–190 of 210 posts

Re: JSONB has landed

#181

Post by anamexis »

cryptonector wrote: post #170
ash wrote: post #166

Earlier quoted context omitted.

Yes, but it's not impossible to achieve it in practice. For example, JavaScript JSON.stringify and JSON.parse have well-defined behaviour: > Properties are visited using the same algorithm as Object.keys(), which has a well-defined order and is stable across implementations https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe... > The traversal order, as of modern ECMAScript specification, is well-defined and…

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

You can count on object key order being preserved when you control the implementation. Which is why it's useful to know whether a given implementation preserves key order.

Re: JSONB has landed

#182

Post by filereaper »

JSONB has performance implications, many of you here are likely familiar with JSONB in Postgres.

I encourage you to view this talk from PGConf NYC 2021 - Understanding of Jsonb Performance by Oleg Bartunov [1]

Looking forward to a similar talk from the SQLite community on JSONB performance in the future.

https://www.youtube.com/watch?v=v_s-DH4PEVA

Re: JSONB has landed

#184

Post by topspin »

chubot wrote: post #144
topspin wrote: post #16

Earlier quoted context omitted.

> JSON has been optimized to death; it seems like you could get the 2x gain and avoid a new format with normal optimization Either I'm experiencing a reading comprehension mishap or this is self contradictory. Where is a "2x gain" supposed to come from through "normal optimization" from after something has already been optimized "to death?" > SIMD JSON techniques Which are infeasible in key SQLite use cases.

I just meant there are a zillion different known ways of optimizing JSON, that maybe could be applied here. But maybe they already optimized it to the degree they're willing and wanted a binary format

SQLite developers aren't in the habit of making poor technical choices. This choice certainly has precedent: Oracle, MySQL, MongoDB, Postgres and others have implemented some form of binary encoding for JSON. Perhaps they know something you don't and deserve the benefit of the doubt.

Re: JSONB has landed

#185

Post by DonHopkins »

Would be nice to have an even more compact csv-esque version of jsonb that knew how to store arrays of objects with the same keys in compressible column-major format, omitting the repeated keys (and even value types if possible), like a transposed csv file with a first column of headers + type info. And the ability to embed arrays of object encoded that way into arbitrary jsonb structures, as long as all the objects have the same keys and (simple enough) value types. Or is that what the (different?) Postgres jsonb format does?

Re: JSONB has landed

#186

Post by seanhunter »

btown wrote: post #163
cryptonector wrote: post #142

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

If you're storing it exactly as provided then surely until you know it's actually valid json you want a string, not a json/jsonb column.

Re: JSONB has landed

#187

Post by ElectricalUnion »

nonethewiser wrote: post #132
oefrha wrote: post #60

Earlier quoted context omitted.

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

It doesnt validate that its valid json?

No, it doesn't. By default in sqlite3, you can store whatever you like in whatever field you want.

The "built-in validation mechanism" is invoking json_valid(X) [1] call within the CHECK [2] condition on a column.

[1] https://www.sqlite.org/json1.html#jvalid

[2] also assumes you didn't disable CHECKs with PRAGMA ignore_check_constraints https://www.sqlite.org/pragma.html#pragma_ignore_check_const...

Re: JSONB has landed

#188

Post by chubot »

topspin wrote: post #32
brazzy wrote: post #31

Earlier quoted context omitted.

> Credit to SQLite developers for adopting an extant binary JSON format, as opposed to inventing yet another one. The comment you are replying to cites a statement saying explicitly that they are not adopting the Postgres JSONB binary format, only the name and the abstract concept. The API is not compatible with Postgres either.

I noted that and removed that bit prior to your reply. Points off for a.) inventing yet another binary JSON and/or b.) using the same name as an existing binary JSON.

Those 2 points are exactly what my comment said

Re: JSONB has landed

#189

Post by ElectricalUnion »

cztomsik wrote: post #107
dspillett wrote: post #93

Earlier quoted context omitted.

> 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)

> other languages use hash maps simply because authors were either lazy or unaware

Because it is idiomatic in that language, and you often don't need the higher overhead of tracking insertion order.

Before Python 3.7, json.loads used dict (undefined order) and you needed to explicitly need to override the load calls with the kwarg `object_pairs_hook=collections.OrderedDict` to accept ordered dictionaries.

Since Python 3.7 all `dict`s are effectively `collections.OrderedDict` because people now expect this kind of inefficient default behavior everywhere. ¯\_(ツ)_/¯

Re: JSONB has landed

#190

Post by iskela »

sltkr wrote: post #109
plq wrote: post #54

Earlier quoted context omitted.

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…

All the keys in JSON are strings. I do not understand why string literal containing number would be moved first. Maybe in JS-object in runtimes.
Post reply on HN