Introduction to the PostgreSQL jsonb_build_object() function

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

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

jsonb_build_object ( VARIADIC"any" ) → jsonb

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 theto_jsonb() function.

Thejsonb_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 PostgreSQLjsonb_build_object() function.

1) Basic jsonb_build_object() function example

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

SELECT  jsonb_build_object(    'title','Academy Dinosaur','length',    86  );

Output:

jsonb_build_object--------------------------------------------- {"title": "Academy Dinosaur", "length": 86}(1 row)

2) Using jonb_build_object() function with table data example

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

SELECT  jsonb_build_object('title', title,'length',length)FROM  filmORDER BY  length DESC;

Output:

jsonb_build_object--------------------------------------------------------- {"title": "Muscle Bright", "length": 185} {"title": "Control Anthem", "length": 185} {"title": "Sweet Brotherhood", "length": 185}...

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

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

SELECT  jsonb_build_object(    'title','Theory Mermaid','length'  );

It returns the following error:

ERROR:  argument list must have evennumber ofelementsHINT:  The arguments of jsonb_build_object() must consist of alternating keysand values.

Summary

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

Last updated onFebruary 23, 2024

On this page

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