Examples illustrate how to use jsonb in PostgreSQL

From yinmingjun, please specify!

A summary of PostgreSQL's support for jsonb

How to understand PostgreSQL's jsonb support? The following case gives the answer.

Let me explain from the mechanism first , jsonb in PostgreSQL is stored as text and parsed during processing , so it is divided into two stages: storage and parsing !

The input and output stages of SQL, which are equivalent to the storage stage of jsonb, are processed as unparsed data.

For PostgreSQL, there is only one type of jsonb at the SQL level, and inside jsonb (at the parsing json level), there are different data types, including: string, number, object, array, etc. At the input and output level of SQL, it can only be transmitted through jsonb. jsonb can be understood as a string whose content conforms to the json parsing rules. You can simply cast jsonb to varchar or text to obtain text in json format.

However, in many cases, the value in json needs to be extracted. This is the most common scenario for data processing. PostgreSQL allows this kind of extraction and returns SQL varchar, for example, through ->> and #>> . The difference between this operation and the direct conversion of obtained jsonb to varchar is that it smoothes out the difference of different types of output.

For example, if the value of the'a' field of json, some correspond to the value 8 , some correspond to the string "8" , some correspond to the value 8.0 , and some correspond to the string "8.0" . In this case, pass ->> What is returned is the result of directly doing str(a) on the corresponding json value , and the return: 8, 8, 8.0, 8.0, it is easier to do subsequent processing. If the json value is directly converted to varchar, it will become: 8, "8", 8.0, "8.0". The difficulty of subsequent unified processing increases a lot. Therefore, at the data processing level, ->> simplifies the difficulty of numerical processing .

Therefore, understanding the role of ->> and #>> can be seen as the internal parsing of json, the str(a) operation is performed on the extracted field value, and the corresponding string literal is forced to be returned uniformly, and converted Varchar into SQL! ! !

jsonb test case

1) jsonb value extraction

Query the string type in json:

SELECT ('{"book": 50, "edition": "8"}'::jsonb->'edition');

Return the jsonb type, the value is "8", which is the json representation of the string:

The data type corresponding to the json returned by the jsonb_typeof query will be returned for json parsing and type inference:

SELECT jsonb_typeof('{"book": 50, "edition": "8"}'::jsonb->'edition');


2) Query fields that do not exist in jsonb

Query non-existent fields and return null corresponding to json:

SELECT '{"book": 50, "edition": "8"}'::jsonb->'NG';

Return null corresponding to json:

Detect the type of null corresponding to json, or null:

SELECT jsonb_typeof('{"book": 50, "edition": "8"}'::jsonb->'NG');


The null value of a field that does not exist is equivalent to the null value of db.

Such as:

SELECT '{"book": 50, "edition": "8"}'::jsonb->'NG' is null;


The type of the json value returned by the probe:

SELECT jsonb_typeof('{"book": 50, "edition": "8"}'::jsonb->'book');


3) The difference between -> and ->> in jsonb

Corresponding to the difference of different types returned in jsonb, you can use ->> to force conversion to varchar to smooth out! ! !


select js->'book', js->'edition', js->'bucket', js->'NG'from (    SELECT '{      "book": 50,      "edition": "8",      "bucket":10.0    }'::jsonb as js) a;


Can be replaced with:

select js->>'book', js->>'edition', js->>'bucket', js->>'NG'from (    SELECT '{      "book": 50,      "edition": "8",      "bucket":10.0    }'::jsonb as js) a;


Because the corresponding types of ->> are varchar in the db type (decoupled from jsonb), it is easy to do the corresponding processing through the type conversion function provided by postgresql.


select (js->>'bucket')::float = 10from (    SELECT '{      "book": 50,      "edition": "8",      "bucket":10.0    }'::jsonb as js) a;


4) The case where the field in jsonb is object

To verify the effect of -> and ->> on the obj type, you can see:

select js->'obj'from (    SELECT '{      "book": 50,      "edition": "8",      "bucket":10.0,      "obj": {        "a": 1,        "b": 2      }    }'::jsonb as js) a;


Look at this again:

select js->>'obj'from (    SELECT '{      "book": 50,      "edition": "8",      "bucket":10.0,      "obj": {        "a": 1,        "b": 2      }    }'::jsonb as js) a;


The difference is that one is jsonb and the other is varchar!