"JavaScript Object Notation", popularly known as JSON is a process of storing data in “key-value” pairs format. JSON is easy for humans to read/understand and is generally utilized for communication between servers and clients. Postgres offers many useful functions and operators to work with JSON data, such as TO_JSON(), ROW_TO_JSON(), ARRAY_TO_JSON, and many more.
This write-up will guide you on using the ROW_TO_JSON() function in PostgreSQL using practical examples.
How Does the ROW_TO_JSON() Function Work in PostgreSQL?
ROW_TO_JSON() is a built-in JSON function that accepts any valid SQL composite type value and converts it into a JSON object.
Syntax
Below is the PostgreSQL ROW_TO_JSON() function's syntax:
ROW_TO_JSON(row RECORD, pretty BOOLEAN);
Parameters
The “row” is a mandatory parameter that must be a value of type composite. While “pretty” is an optional parameter that beautifies the retrieved result of the ROW_TO_JSON() function.
Let’s implement this function practically!
Example 1: Using ROW_TO_JSON() on Composite Types
The following example illustrates the use of the ROW_TO_JSON() function on composite type values :
SELECT ROW_TO_JSON(ROW(110, 102, 'Joseph', 25, 'Joseph is an Author'));
Here in the above code:
- We utilized the “ROW” expression to construct a composite type.
- The composite type value contains five elements.
- After that, the ROW_TO_JSON() is utilized on the composite type data.
- The ROW_TO_JSON() function auto-generates keys for the JSON object in “fn” format, where “n = 1, 2, 3, …”.
The output shows that the composite type has been successfully converted into a JSON object:
Example 2: Using ROW_TO_JSON() With pretty Parameter
Pass the Boolean “true” as a pretty argument to beautify the converted JSON object:
SELECT ROW_TO_JSON(ROW(110, 102, 'Joseph', 25, 'Joseph is an Author'), true);
That’s all about converting any SQL value to JSON Object.
Conclusion
In PostgreSQL, the ROW_TO_JSON() is a built-in JSON function that accepts any valid SQL composite type value and converts it into a JSON object. To use ROW_TO_JSON() in Postgres, use the “ROW_TO_JSON(row RECORD, pretty BOOLEAN);” syntax. Where the “row” is a mandatory parameter that must be a value of type composite. While “pretty” is an optional parameter that beautifies the retrieved result of the ROW_TO_JSON() function. This post has demonstrated a practical guide on different use cases of the ROW_TO_JSON() function in Postgres.
