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

161–170 of 210 posts

Re: JSONB has landed

#161

Post by fl0ki »

cryptonector wrote: post #142
btown wrote: post #139

Earlier quoted context omitted.

There’s a huge nuance worth mentioning: with JSONB you lose key ordering in objects. This may be desired - it makes two effectively-equal objects have the same representation at rest! But if you are storing human-written JSON - say, configs from an internal interface, where one might collocate a “__foo_comments” key above “foo” - their layout will be lost, and this may lead to someone visually seeing their changes sc…

JSON processors are not required nor expected to retain object key ordering from either input or object construction order.

That compounds really badly with another lack of specificity in JSON: parsers can treat duplicate keys in arbitrary ways, e.g. keep only the first or last value, combine values somehow, error out, etc.

When keys are not unique, their order can matter again, and can matter differently to different parsers.

Re: JSONB has landed

#162

Post by ckok »

Does anyone know if SQLite has a public roadmap? This combined with a gin or gin like index would be a killer combination

Re: JSONB has landed

#163

Post by btown »

cryptonector wrote: post #142
btown wrote: post #139

Earlier quoted context omitted.

There’s a huge nuance worth mentioning: with JSONB you lose key ordering in objects. This may be desired - it makes two effectively-equal objects have the same representation at rest! But if you are storing human-written JSON - say, configs from an internal interface, where one might collocate a “__foo_comments” key above “foo” - their layout will be lost, and this may lead to someone visually seeing their changes sc…

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's not unreasonable that someone would expect this of their database!

Re: JSONB has landed

#164

Post by mdaniel »

dheera wrote: post #123
lovasoa wrote: post #84

Earlier quoted context omitted.

> If you're just using SQLite to write and read full JSON blobs, the JSON datatype will be the best pick. There is no json data type ! If you are just storing json blobs, then the BLOB or TEXT data types will be the best picks.

> There is no json data type Why not? I feel like a database should allow for a JSON data type that stores only valid JSON or throws an exception. It would also be nice to be able to access subfields of the JSON. SELECT userid, username, some_json_field["some_subfield"][0] FROM users where ... Not sure where to give feature suggestions so I'm just leaving this here for future devs to find

The H2 database uses almost that exact syntax:

  create table foo(my_json json);
  insert into foo values ('{"a":{"b":{"c":"d"}}}' FORMAT JSON);
  select (my_json)."a"."b"."c" from foo; -- where the () around the field is mandatory
https://h2database.com/html/grammar.html#field_reference

Re: JSONB has landed

#165

Post by chungy »

dheera wrote: post #123
lovasoa wrote: post #84

Earlier quoted context omitted.

> If you're just using SQLite to write and read full JSON blobs, the JSON datatype will be the best pick. There is no json data type ! If you are just storing json blobs, then the BLOB or TEXT data types will be the best picks.

> There is no json data type Why not? I feel like a database should allow for a JSON data type that stores only valid JSON or throws an exception. It would also be nice to be able to access subfields of the JSON. SELECT userid, username, some_json_field["some_subfield"][0] FROM users where ... Not sure where to give feature suggestions so I'm just leaving this here for future devs to find

> It would also be nice to be able to access subfields of the JSON.

Not that it's not _useful_ sometimes, but it amuses me that this is a huge violation of 1NF and people are often ok with it. It really depends on whether you're treating the JSON object as an atomic unit on its own, regardless of contents, or using the JSON to store more fine-grained information.

I guess the same argument can be made for XML data types, and everyone's OK with it too.

Re: JSONB has landed

#166

Post by ash »

cryptonector wrote: post #142
btown wrote: post #139

Earlier quoted context omitted.

There’s a huge nuance worth mentioning: with JSONB you lose key ordering in objects. This may be desired - it makes two effectively-equal objects have the same representation at rest! But if you are storing human-written JSON - say, configs from an internal interface, where one might collocate a “__foo_comments” key above “foo” - their layout will be lost, and this may lead to someone visually seeing their changes sc…

JSON processors are not required nor expected to retain object key ordering from either input or object construction order.

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 consistent across implementations. Within each component of the prototype chain, all non-negative integer keys (those that can be array indices) will be traversed first in ascending order by value, then other string keys in ascending chronological order of property creation.

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe...

Similarly, Python json module guarantees that "encoders and decoders preserve input and output order by default. Order is only lost if the underlying containers are unordered." Since 3.7 dict maintains insertion order.

https://docs.python.org/3/library/json.html

https://docs.python.org/3/library/stdtypes.html#dict

Yes, JS and Python behaviour are not the same for all cases, however, non-integer keys do maintain order across Python and JavaScript.

Re: JSONB has landed

#167

Post by ash »

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…

> Javascript environments ... guarantee that they'll preserve object key order when JSON is evaluated

Yes, but only for non-integer keys.

Re: JSONB has landed

#168

Post by ash »

btown wrote: post #139
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.

There’s a huge nuance worth mentioning: with JSONB you lose key ordering in objects. This may be desired - it makes two effectively-equal objects have the same representation at rest! But if you are storing human-written JSON - say, configs from an internal interface, where one might collocate a “__foo_comments” key above “foo” - their layout will be lost, and this may lead to someone visually seeing their changes sc…

What about SQLite JSONB? Does it maintain order?

UPDATE: I think SQLite JSONB does maintain order. For example:

    select json(jsonb('{"a": 1, "b": 2, "c": 3, "d": 4}'));"
    -- {"a":1,"b":2,"c":3,"d":4}
And it does maintain order when adding new keys:

    select json(jsonb_insert(jsonb('{"a": 1, "b": 2, "c": 3, "d": 4}'), '$.e', 99));
    -- {"a":1,"b":2,"c":3,"d":4,"e":99}
(tested on https://codapi.org/sqlite/)

Re: JSONB has landed

#169

Post by masklinn »

cryptonector wrote: post #140
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…

TFA doesn't say that this is _the_ JSONB of PostgreSQL fame, but I assume it must be. JSONB is a brilliant binary JSON format. What makes it brilliant is that arrays and objects (which when serialized are a sort of array) are encoded by having N-1 lengths of values then 1 offset to the Nth value, then N-1 lengths of values then... The idea is that lengths are going to be similar, while offsets never are, so using len…

> TFA doesn't say that this is _the_ JSONB of PostgreSQL fame, but I assume it must be.

Definitely is not: Hipp states

> JSONB is also slightly smaller than text JSON in most cases (about 5% or 10% smaller)

Whereas in Postgres jsonb commonly takes 10~20% more space than json.

Re: JSONB has landed

#170

Post by cryptonector »

ash wrote: post #166
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.

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.

Post reply on HN