JSON or "JavaScript Object Notation" is a process of storing data in “key-value” pairs format. It's easy for humans to read/understand and is often used for communication between servers and clients. Postgres has native support for JSON starting from version 9.2 and onward releases. Postgres offers many useful functions and operators to work with JSON data efficiently.

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

How Does the TO_JSON() Function Work in PostgreSQL?

TO_JSON() is a built-in JSON function that accepts any valid SQL value and converts it into JSON format. Below is the PostgreSQL TO_JSON() function's syntax:

TO_JSON(value);

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

Let’s implement this function practically!

Example 1: Using TO_JSON() on SQL Values

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

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

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

The provided values have been successfully converted into JSON type.

Example 2: Using TO_JSON() on Arrays

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

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

The given array has been successfully converted into JSON format.

Example 3: Using TO_JSON() on Composite Types

In the following example, we utilized the “ROW” expression to construct a composite type. The composite type value contains five elements. After that, the TO_JSON() is utilized on the composite type data:

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

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

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

Conclusion

In PostgreSQL, the TO_JSON() is a built-in JSON function that accepts any SQL value as an argument and converts it to JSON format. To use TO_JSON() in Postgres, use the “TO_JSON(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_JSON() function in Postgres.