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

171–180 of 210 posts

Re: JSONB has landed

#171

Post by masklinn »

cryptonector wrote: post #155
masklinn wrote: post #74

Earlier quoted context omitted.

AFAIK jsonb is not a specific format, it’s just a generic term for “a json equivalent binary representation”. The sqlite blurb says jsonb is generally smaller than json, but IIRC from when postgres added jsonb postgres’ is generally slightly larger.

PG's JSONB has a specification (well, inside PG, not a standard), and they won't change it except in backwards compatible ways (or, really, at all).

> PG's JSONB has a specification (well, inside PG, not a standard)

So does sqlite's. An implementation detail, even a necessarily stable one, does not a format make.

Re: JSONB has landed

#172

Post by sgbeal »

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…

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

Re: JSONB has landed

#173

Post by ash »

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.

There are situations where keeping order is useful. For example, human-editable json config files.

I also gave an example of two implementations that are compatible for a useful subset of keys. By the way, SQLite JSONB keeps object keys in insertion order, similar to Python: https://news.ycombinator.com/item?id=38547254

Re: JSONB has landed

#174

Post by ash »

jamesfinlayson wrote: post #36
blowski wrote: post #33

Earlier quoted context omitted.

Is there any downside to storing JSON-B even if you’re not planning to query it? For example, size on disk, read/write performance?

If the order of items in the JSON blob matters then JSONB probably wouldn't preserve the order.

SQLite JSONB does maintain order: https://news.ycombinator.com/item?id=38547254

Re: JSONB has landed

#175

Post by marcosdumay »

chungy wrote: post #165
dheera wrote: post #123

Earlier quoted context omitted.

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

The principles of relational algebra aren't always compatible with real applications.

They are successful beyond anything that I can imagine people expecting when creating them. But they are not that complete silver bullet that solves every problem humanity will ever need solved.

Re: JSONB has landed

#177

Post by pie_flavor »

sgbeal wrote: post #172
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…

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

Re: JSONB has landed

#178

Post by 0x457 »

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

SQLite has a very few actual data types. There is a function that validates json and you can use it as a check on your `TEXT` or` `BLOB` column.

You can access subfields using `json_` and `jsonb_` functions.

Re: JSONB has landed

#179

Post by sgbeal »

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.

> Using a function to query a fact about a string does not imply that the string should change, ... then there's nothing implementation-quirk about that.

Fair enough when considering only the string value. My ill-expressed point was more about JSON as a data exchange format - it may pass through any number of JSON parser/storage implementations, any of which may use an arbitrary order for the keys.

Re: JSONB has landed

#180

Post by 0x457 »

nikeee wrote: post #72
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…

If my application never sees a difference to normal JSON, everything is compatible and all there is are perf improvements, why is there a new set of functions to interact with it (jsonb_*)? It seems that the JSON type is even able to contain JSONB. So why even use these functions, if the normal ones don't care?

`json_` functions return json, `jsonb_` functions return jsonb. Both take either as input.

If you're modifying something "in-place" then `jsonb_` functions would be better since they avoid conversion.

Post reply on HN