Introduction to the PostgreSQL row_to_json() function

Therow_to_json() function allows you to convert an SQL composite value into a JSON object.

Here’s the syntax of therow_to_json() function:

row_to_json ( record [, boolean ] ) →json

In this syntax:

• record is an SQL composite value that you want to convert into a JSON object.
• boolean if true, the function will add a line feed between top-level elements.

Therow_to_json() function will return a JSON object.

PostgreSQL row_to_json() function examples

Let’s take some examples of using therow_to_json() function.

1) Basic row_to_json() function example

The following example uses therow_to_json() function to convert a row into a JSON object:

SELECT row_to_json(row('John',20));

Output:

row_to_json----------------------- {"f1":"John","f2":20}(1 row)

In this example, we use therow() function to create a composite value made up of multiple columns.

Therow_to_json() function returns an object whose keys are automatically generated f1 and f2 with the values from the composite values.

2) Using the row_to_json() function with table data

We’ll use thefilm table from thesample database:

The following example uses therow_to_json() function to convert thetitle andlength of each film in thefilm table into a JSON object:

SELECT  row_to_json(t) filmFROM  (    SELECT      title,      length    FROM      film    ORDER BY      title  ) t;

Output:

film------------------------------------------------------ {"title":"Academy Dinosaur","length":86} {"title":"Ace Goldfinger","length":48} {"title":"Adaptation Holes","length":50} {"title":"Affair Prejudice","length":117} {"title":"African Egg","length":130} {"title":"Agent Truman","length":169}...

How it works.

• The subquery retrieves thetitle andlength from thefilm table.
• The outer query uses therow_to_json() to convert each row returned by the subquery into a JSON object.

Note that you can use acommon table expression (CTE) instead of a subquery to achieve the same result:

WITH film_cteAS (  SELECT    title,    length  FROM    film  ORDER BY    title)SELECT  row_to_json(film_cte)FROM  film_cte;

Summary

• Use therow_to_json() function to convert an SQL composite value to a JSON object.

Last updated onFebruary 25, 2024

On this page

• Introduction to the PostgreSQL row_to_json() function
• PostgreSQL row_to_json() function examples
• Summary