Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "what is the difference between JSON and JSONB". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

JSON and JSONB are also enemies. Nowadays, many open source systems and open source software support JSON and JSONB, such as Postgresql and MongoDB. This also led to a lot of people at a loss when asked in the interview about the difference between JSON and JSONB!

Today, let's take a look at the differences between them.

There is an explanation on stackoverflow, which I posted below to make it easier to read.

First of all, hstore is an extension module that allows you to save key= > values key-value pairs, all of which can only be of type texts (however, values also allow sql's NULL)

Json and jsonb allow you to save a valid json value (definition).

For example, the following are valid json representations: null, true, [1, false, "string", {"foo": "bar"}], {"foo": "bar", "baz": [null]}.

Compared to json, hstore is only a small subset of it (but if you only need this subset, it's also OK)

The main difference between json and jsonb is the way they are stored:

Json is saved in text format

Jsonb is saved in binary format

This has three main effects:

Jsonb usually takes up more disk space than json (in some cases it is not)

Jsonb takes more time to write than json

The operation of json is significantly more time-consuming than that of jsonb (it needs to be parsed every time when manipulating a value of type json)

There are two main usage cases when jsonb will be available in a stable release in the future, and you can easily choose between them:

If your application is represented only by json and PostgreSQL is only used for saving and fetching, you should use json.

If you need to do more json value operations in PostgreSQL, or when using indexes on some json fields, you should use jsonb

The official document says:

There are two JSON data types: json and jsonb. They accept almost the same set of values as input. The main difference between them is efficiency. The json data type stores an exact copy of the input text, and the processing function must reparse on each execution, while the jsonb data is stored in a shredded binary format, which makes it slightly slower on input due to the addition of a conversion mechanism, but significantly faster in processing because it does not need to be reparsed. Jsonb also supports indexing, which is also a clear advantage.

Because the json type stores an exact copy of the input text, it holds the semantically insignificant spaces between tokens and the order of keys in the JSON object. In addition, if a JSON object in the value contains the same key multiple times, all key / value pairs are saved. (the handler treats the last value as an operation value. By contrast, jsonb does not save spaces, the order of object keys, and duplicate object keys If a duplicate key is specified in the input, only the last value is saved.

The common functions and operators of json (jsonb)-> right operator are int: get JSON array elements (index starts at 0)

The right operator is text: get the json value through the key

-> > right operator is int: get JSON array element is text

The right operator is text: get the json value as text through the key

Other operators, such as the common operators in Postgresql: # >, # > >, @ >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.