8 ms·

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 appli

• blowski 3y ago
  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?
  • jamesfinlayson 3y ago
    If the order of items in the JSON blob matters then JSONB probably wouldn't preserve the order.
    • mike_d 3y ago
      JSON is unordered. Nothing in your code should assume otherwise. "An object is an unordered collection of zero or more name/value pairs, where a name is a string and a value is a string, number, boolean, null, object, or array."
      • hakunin 3y ago
        That’s exactly the kind of difference between json and jsonb that you gotta keep in mind. Object properties are unordered, but a json string is very much ordered. It’s the same sequence of characters and lines each time, unless you parse it and dump it again. So if you want to preserve an unmodified original json string for some (e.g. cosmetic) reasons, you probably want json.
        • rowanG077 3y ago
          I would expect you are threading on dangerous grounds to assume a type called JSON is going to preserve the data byte for byte. It might currently but I doubt that is in the API contract. You really want to use TEXT if that is your requirement
          • cztomsik 3y ago
            I don't know what SQLite does, but in JS the order is actually defined. JS is not Java, object is not HashMap, if anything, it's closer to LinkedHashMap, but even that is not correct because there are numeric slots which always go first. https://tc39.es/ecma262/#sec-ordinaryownpropertykeys https://tc39.es/ecma262/#sec-ordinaryownpropertykeys
            • dspillett 3y ago
              > 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 does not have a prescribed order for properties, so it is not safe to assume one. JS may generally impose a particular order, but other things may not when [re]creating a JSON string from their internal format (JSONB in this case), so by assuming a particular order will be preserved you would be relying on a behaviour that is undefined in the context of JSON.
              • cztomsik 3y ago
                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)
                • 3y ago
      • cdogl 3y ago
        The same goes for maps in Go, which now explicitly randomizes map iteration with the range keyword to prevent developers from relying on a particular ordering. Neat trick.
        • mike_hock 3y ago
          That might get them to rely on the randomization, though :)
      • deleted 3y ago
        [deleted]
      • jamesfinlayson 3y ago
        I don't disagree, but people might still assume it. If you serialise a Map in Java, some Map implementations will maintain insertion order for example.
        • ifwinterco 3y ago
          I think this is why JS and Python chose to make key ordering defined - the most popular implementations maintained insertion order anyway, so it was inevitable people would end up writing code relying on it
      • plq 3y ago
        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 depending on your use case. Unless eg. you need interop with the rest of the world, it's your JSON, (mis)treat it to your heart's content.
        • mike_d 3y ago
          > You make it sound like it's one of the laws of physics. The text I quoted is from the RFC. json.org and ECMA-404 both agree. You are welcome to do whatever you want, but then it isn't JSON anymore.
          • The_Colonel 3y ago
            That does not follow. JSON formatted data remains JSON no matter if you use it incorrectly. This happens all the time in the real world - applications unknowingly rely on undefined (but generally true) behavior. If you e.g. need to integrate with a legacy application where you're not completely sure how it handles JSON, then it's likely better to use plain text JSON. You may take the risk as well, but then good luck explaining that those devs 10 years out of the company are responsible for the breakage happening after you've converted JSON to JSONB. In some cases, insisting on ignoring the key order is too expensive luxury, since it basically forces you to parse the whole document first and only then process it. In case you have huge documents, you have to stream-read and this often implies relying on a particular key order (which isn't a problem since those same huge documents will likely be stream-written with a particular order too).
          • throwaway290 3y ago
            It is very much still JSON, and your code can very much assume keys are ordered if your JSON tools respect it. ECMA-404 agrees: > The JSON syntax does not impose any restrictions on the strings used as names, does not require that name strings be unique, and does not assign any significance to the ordering of name/value pairs. These are all semantic considerations that may be defined by JSON processors or in specifications defining specific uses of JSON for data interchange. If you work in environments that respect JSON key order (like browser and I think also Python) then unordered behavior of JSONB would be the exception not the rule.
      • ako 3y ago
        Assume you're writing an editor for JSON files. Don't think many users of that editor would be very happy if you change the order of the attributes in their json files, even though technically it's the same...
        • jchanimal 3y ago
          According to the JSON spec it's also legal to round all numbers to zero.
        • golergka 3y ago
          If you’re writing an editor, you will need to separate in progress text that user is editing from the parsed and validated data, and keep both.
    • ash 3y ago
      SQLite JSONB does maintain order: https://news.ycombinator.com/item?id=38547254 https://news.ycombinator.com/item?id=38547254
  • oefrha 3y ago
    There’s processing to be done with JSONB on every read/write, which is wasted if you’re always reading/writing the full blob.
    • lifthrasiir 3y ago
      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.
      • oefrha 3y ago
        No, SQLite's "JSON" is just TEXT, there's no overhead with reading/writing a string.
        • lifthrasiir 3y ago
          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.
          • oefrha 3y ago
            There’s no point using json_* functions if you’re always reading/writing the full blob.
            • lifthrasiir 3y ago
              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.
          • 3y ago
  • chippiewill 3y ago
    Not specifically JSONB, but I do recall that with MongoDB's equivalent, BSON, the sizes of the binary equivalent tend to be larger in practice, I would expect JSONB to have a similar trade off. There'll also be a conversion cost if you ultimately want it back in JSON form.
    • cowsandmilk 3y ago
      From the post: > JSONB is also slightly smaller than text JSON in most cases (about 5% or 10% smaller) so you might also see a modest reduction in your database size if you use a lot of JSON.
  • kevincox 3y ago
    According to the linked announcement the data size is 5-10% smaller. So you are exchanging a small processing cost for smaller storage size. It will depend on your application but in many cases smaller disk reads and writes as well as more efficient cache utilization will make storing as JSONB a better choice even if you never manipulate it in SQL. Although the difference is likely small.
  • Someone 3y ago
    I haven’t delved into it, but you likely lose the ability to round-trip your json strings, just as in postgreSQL’s jsonb type. Jsonb_extract(jsonb(foo), '€') likely - will remove/change white space in ‘foo’, - does not guarantee to keep field order - will fail if a dictionary in the ‘foo’ string has duplicate fields - will fail if ‘foo’ doesn’t contain a valid json string
  • cryptonector 3y ago
    PG's JSONB is compressible yet also indexed (objects' key/value pairs are sorted on key and serialized array-like; arrays are indexed by integers; therefore binary searching large objects works). The history of PG's JSONB type is very interesting.
• nikeee 3y ago
  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?
  • dtech 3y ago
    The difference is the storage format, which is important for a SQL database since you define it in the schema. Also the performance characteristics are different.
  • The_Colonel 3y ago
    As someone mentioned below, the order of keys is undefined in JSON spec, but applications may rely on it anyway, and thus conversion to JSONB may lead to breakage. There are some other minor advantages of having exact representation of the original - e.g. hashing, signatures, equality comparison is much simpler on the JSON string (you need a strict key order, which is again undefined by the spec, but happens in the real world anyway).
  • 0x457 3y ago
    `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.
• tuyiown 3y ago
  > If you're just using SQLite to write and read full JSON blobs, the JSON datatype will be the best pick. I don't know how the driver is written, but this is misleading if sqlite provides an api to read the jsonb data with a in-memory copy, an app can surely benefit from skipping the json string parsing.
• tyingq 3y ago
  > Your application will never see the raw JSONB content. That's not exactly right, as the jsonb_* functions return JSONB if you choose to use them.
  • cryptonector 3y ago
    And you can just read the BLOB column values out of the DB.
• lovasoa 3y ago
  > 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.
  • gwd 3y ago
    In context, what is clearly meant is, "If you're just reading and writing full JSON blobs, use `insert ... ($id,$json)`; if you're primarily querying the data, use `insert ... ($id, jsonb($json))`, which will convert the text into JSONB before storing it.
  • dheera 3y ago
    > 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
    • sroussey 3y ago
      SQLite has a surprisingly small number of types: null, integer, real, text, and blob. That’s it. https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
    • mdaniel 3y ago
      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 https://h2database.com/html/grammar.html#field_reference
    • chungy 3y ago
      > 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.
• zmmmmm 3y ago
  I don't know if it's true of SQLite, but you missed the most important point at least with PostgreSQL : you can build indexes directly against attributes inside JSONB which really turns it into a true NoSQL / relational hybrid that can let you have your cake and eat it too for some design problems that don't fit neatly into either pure relational or pure NoSQL approaches.
  • euroderf 3y ago
    What would that SQL look like in practice ?
    • ako 3y ago
      Here's an example i have running on a pi with a temperature sensor. All data is received as json messages in mqtt, then stored as is in a postgres table. The view turns it into relational data. If i just need the messages between certain temperatures i can speed this up by adding an index on the 'temperature' field in json. create or replace view bme680_v(ts, message, temperature, humidity, pressure, gas, iaq) as SELECT mqtt_raw.created_at AS ts , mqtt_raw.message , (mqtt_raw.message::jsonb ->> 'temperature'::text)::numeric AS temperature , (mqtt_raw.message::jsonb ->> 'humidity'::text)::numeric AS humidity , (mqtt_raw.message::jsonb ->> 'pressure'::text)::numeric AS pressure , (mqtt_raw.message::jsonb ->> 'gas_resistance'::text)::numeric AS gas , (mqtt_raw.message::jsonb ->> 'IAQ'::text)::numeric AS iaq FROM mqtt_raw WHERE mqtt_raw.topic::text = 'pi/bme680'::text ORDER BY mqtt_raw.created_at DESC;
  • debugnik 3y ago
    You can do this with text JSON in SQLite as well, but JSONB could speed it up.
• renegade-otter 3y ago
  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.
  • kevincox 3y ago
    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 you probably want to use `json_*` functions SELECT json_extract(col1, '$.myProp') FROM t Otherwise you will end up receiving the binary format.
    • yencabulator 3y ago
      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.
  • btown 3y ago
    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 scrambled on save.
    • cryptonector 3y ago
      JSON processors are not required nor expected to retain object key ordering from either input or object construction order.
• cryptonector 3y ago
  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 lengths makes JSONB more compressible, but offsets are needed to enable random access, so if you store mostly lengths and sometimes offsets you can get O(1)ish random access while still retaining compressibility. Originally JSONB used only offsets, but that made it incompressible. That held up a PostgreSQL release for some time while they figured out what to do about it.
  • ks2048 3y ago
    Link? My reading last time I looked at this was that the sqlite and postgres “jsonb” were different.
    • cryptonector 3y ago
      This is the first time that SQLite3 is getting JSONB. Idk if it's the same as PG's. TFA doesn't say. I assume they are the same or similar because I seriously doubt that D.R. Hipp is unaware of PG's JSONB, but then for that reason I am surprised that he didn't say in TFA.
      • cryptonector 3y ago
        https://sqlite.org/draft/jsonb.html https://sqlite.org/draft/jsonb.html says they're NOT the same.
      • anarazel 3y ago
        https://sqlite.org/draft/jsonb.html https://sqlite.org/draft/jsonb.html references postgres: > The "JSONB" name is inspired by PostgreSQL, but the on-disk format for SQLite's JSONB is not the same as PostgreSQL's. The two formats have the same name, but they have wildly different internal representations and are not in any way binary compatible.