PostgreSQL jsonb_build_object() Function

Summary: in this tutorial, you will learn how to use the PostgreSQL jsonb_build_object() function to create a JSON object based on a variadic argument list.

Introduction to the PostgreSQL jsonb_build_object() function

The jsonb_build_object() function allows you to build an object out of a variadic argument list.

Here’s the basic syntax of the jsonb_build_object() function:

jsonb_build_object ( VARIADIC "any" ) → jsonbCode language: SQL (Structured Query Language) (sql)

In this syntax, you pass a list of alternative keys and values. The list must have an even number of elements or you’ll get an error.

The function will coerce the key to text and value to a JSON value using the to_jsonb() function.

The jsonb_build_object() function returns a JSON object with the specified key and values.

PostgreSQL jsonb_build_object() function examples

Let’s explore some examples of using the PostgreSQL jsonb_build_object() function.

1) Basic jsonb_build_object() function example

The following example uses the jsonb_build_object() function to build an object out of the alternating keys and values:

SELECT 
  jsonb_build_object(
    'title', 'Academy Dinosaur', 'length', 
    86
  );Code language: SQL (Structured Query Language) (sql)

Output:

             jsonb_build_object
---------------------------------------------
 {"title": "Academy Dinosaur", "length": 86}
(1 row)Code language: SQL (Structured Query Language) (sql)

2) Using jonb_build_object() function with table data example

The following example uses the jsonb_build_object() function to create a JSON object based on the title and length of films in the film table from the sample database:

SELECT 
  jsonb_build_object('title', title, 'length', length) 
FROM 
  film 
ORDER BY 
  length DESC;Code language: SQL (Structured Query Language) (sql)

Output:

                   jsonb_build_object
---------------------------------------------------------
 {"title": "Muscle Bright", "length": 185}
 {"title": "Control Anthem", "length": 185}
 {"title": "Sweet Brotherhood", "length": 185}
...Code language: SQL (Structured Query Language) (sql)

3) Using the jonb_build_object() function with an odd number of values

The following example attempts to use the jsonb_build_object() function with an odd number of values:

SELECT 
  jsonb_build_object(
    'title', 'Theory Mermaid', 'length'
  );Code language: SQL (Structured Query Language) (sql)

It returns the following error:

ERROR:  argument list must have even number of elements
HINT:  The arguments of jsonb_build_object() must consist of alternating keys and values.Code language: SQL (Structured Query Language) (sql)

Summary

• Use the PostgreSQL jsonb_build_object() function to build a JSON object out of a variadic argument list.