"JavaScript Object Notation", popularly known as JSON is a data type in Postgres for storing data in “key-value” pairs format. While JSONB or “JSON BINARY” is a data type that stores data in binary format. Postgres offers various inbuilt functions and operators to work with JSON and JSONB data efficiently.

This write-up will guide you on using the TO_JSONB() function in PostgreSQL using practical examples.

What Does the TO_JSONB() Function Do in PostgreSQL?

TO_JSONB() is a built-in JSON function that accepts any valid SQL value and converts it into JSONB format. Follow the below-provided syntax to implement TO_JSONB() function in PostgreSQL:

TO_JSONB(value);

Here, the “value” can be any valid SQL value.

Let’s implement this function practically!

Example 1: Using TO_JSONB() on SQL Values

The following example illustrates the basic usage of the TO_JSONB() function in PostgreSQL:

SELECT
TO_JSONB(510),
TO_JSONB(13.52),
TO_JSONB('Command Prompt'::text),
TO_JSONB(True),
TO_JSONB(False);

In this example, we passed an integer, a floating point value, a text value, and a couple of BOOLEAN values to the TO_JSONB() function. Consequently, the stated function converts all the values into JSONB format:

The provided values have been successfully converted into JSONB type.

Example 2: Using TO_JSONB() on Arrays

This example demonstrates the use of TO_JSONB() function on the arrays’ data:

SELECT TO_JSONB(ARRAY[[110, 102], [113, 410], [131, 103]]);

The given array has been successfully converted into JSONB format.

Example 3: Using TO_JSONB() on Composite Types

In the following example, the JSONB() function is used on the composite type values:

SELECT TO_JSONB(ROW(110, 102, 'Joseph', 25, 'Joseph is an Author'));

- First, we utilized the “ROW” expression to construct a composite type.

- The composite type value contains five elements.

- After that, the TO_JSONB() is utilized on the composite type data.

The output shows that the composite type has been successfully converted into JSONB type:

That’s all about converting any SQL value to JSONB form.

Conclusion

In PostgreSQL, the TO_JSONB() is a built-in JSON function that accepts any SQL value as an argument and converts it to JSONB format. To use TO_JSONB() in Postgres, use the “TO_JSONB(value);” syntax. Where the “value” can be any valid SQL value. This post has demonstrated a practical guide on different use cases of the TO_JSONB() function in Postgres.