PostgreSQL allows the user to store data in the database using different formats, such as JSON, JSONB, etc. In Postgres, JSON is used to store data of an unspecified type and the user does not have the means to understand its type. JSONB is the binary version of the JSON data type to improve the performance while extracting the data from the PostgreSQL database.

This guide will explain the differences between JSON and JSONB types in PostgreSQL with examples.

JSON Vs. JSONB in PostgreSQL

JSON is a semi-structured data that is a widely adopted data interchangeable format and it is a lightweight and flexible data type. JSON data type is only used when the user doesn't have knowledge about the format of the data and there are no other options to get to know it. JSONB is its binary form which comes as the better version of JSON in terms of performance as it reads data at a faster pace. JSONB stores data in the decomposed binary format so it requires more time to write data in JSONB format.

Operators Supported by JSON & JSONB

To understand the differences between JSON and JSONB, the following sections contain examples of using JSON and JSONB operators in PostgreSQL:

JSON Operators

Type the following query to use the JSON operator (->) in the PostgreSQL database:

select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2

The above query gets a JSON array element with indexes starting from zero and the json->2 indicated the 3rd position in the array:

Use the following query to use the JSON operator (->>) on the array:

select'[1,2,3]'::json->>2

The above query selects an array containing multiple numbers and uses the “->>” operator to get the JSON element as the text:

Use the following query that contains a JSON operator #> to get data from the given array:

select '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'

The above query obtains a JSON object from the specified path which in this case is the data inside the innermost bracket:

JSONB Operators

Use the following query to use the JSONB operator @> to get the boolean value:

select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb

The above code specifies whether the left JSON value contains the right JSON path or value:

Use the following query to use the JSONB operator || which is used to merge two conditions:

select '["a", "b"]'::jsonb || '["c", "d"]'::jsonb

The above query contains the || operator that is used to concatenate two or more conditions and the answer will be true if either condition is true:

Functions Supported by JSON & JSONB

After using the operators, this section uses different functions to demonstrate the differences between JSON and JSONB:

JSON Functions

Use the following query to use the json_array_length() function in PostgreSQL:

select json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');

The above query uses the json_array_length function to get the size/length of the array:

Type the following command to use the json_typeof() function which is a JSON function:

select json_typeof('-123.4');

The above query uses the json_typeof() function to get the data type of the data:

JSONB Functions

Execute the following query that uses the jsonb_set() function to modify a value within a JSONB document:

select jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false);

The above query returns the objects in the target parameter and this section is specified by the path parameter:

Use the following query to use the jsonb_insert() function in JSONB data type:

select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"');

The above query uses the jsonb_insert() function to insert a new value at the location provided between the array and new_value:

That’s all about the differences between JSON and JSONB in PostgreSQL using examples.

Conclusion

The JSON and JSONB are the data types in the PostgreSQL database that are used to store unidentified data in a semi-structured format. The JSON and JSONB types have different operators and functions to extract information from the stored data in the PostgreSQL database. This guide has demonstrated with examples some of the comparisons between JSON and JSONB data types in PostgreSQL.