PostgreSQL jsonb_object() Function

The PostgreSQL jsonb_object() function builds a JSONB object from a text array, or from two arrays as keys and values respectively.

jsonb_object() Syntax

The syntax of the PostgreSQL jsonb_object() function comes in two forms:

jsonb_object(kv_array TEXT[]) -> JSONB

or

jsonb_object(k_array TEXT[], v_array TEXT[]) -> JSONB

Parameters

kv_array

Required. A text array. It can be a one-dimensional array, which must have an even number of elements, consisting of alternating keys and values. It can also be a two-dimensional array, and each inner array must have two elements as keys and values.

k_array

Required. A text array containing the keys. The elements in it and the elements in the value array form a key-value pair in a one-to-one correspondence.

v_array

Required. A text array containing the values. The elements in it and the elements in the key array form a key-value pair in one-to-one correspondence.

Return value

The PostgreSQL jsonb_object() function returns a JSONB object.

The jsonb_object() function evaluates each parameter in the variable parameter list, and all values ​​are converted to JSONB strings.

The number of elements in the array in the single array argument form must be an even number, otherwise PostgreSQL will give an error.

The key array and the value array in the two array parameter form must have the same number of elements, otherwise PostgreSQL will give an error.

jsonb_object() Examples

Example 1

The following example shows how to use the PostgreSQL jsonb_object() function to build a JSONB object from a one-dimensional array.

SELECT jsonb_object(ARRAY[1, 'a', true, row(2, 'b', false)]::TEXT[]);
           jsonb_object
---------------------------------
 {"1" : "a", "true" : "(2,b,f)"}

Here for an array in the one-argument form, the elements of the array are used as alternating keys and values:

  • 1 is used as a key
  • 'a' is used as a value of the key 1
  • true is used as a key
  • row(2, 'b', false) is used as a value of the key true

Example 2

The following example shows how to use the PostgreSQL jsonb_object() function to build a JSONB object from a two-dimensional array.

SELECT jsonb_object('{{1, 2}, {3, 4}, {a, 6}}');
            jsonb_object
-----------------------------------
 {"1" : "2", "3" : "4", "a" : "6"}

Here, the two elements of the inner array in each 2D array serve as keys and values:

  • In {1, 2}, 1 is used as key, 2 is used as value
  • In {3, 4}, 3 is used as key, 4 is used as value
  • In {a, 6}, a is used as key, 6 is used as value

Example 3

This example shows how to use the PostgreSQL jsonb_object() function to build a JSONB object from two one-dimensional arrays.

SELECT jsonb_object('{x, y}', '{1, 2}');
      jsonb_object
------------------------
 {"x" : "1", "y" : "2"}

Here, the elements in the key array {x, y} and the value array {1, 2} form key-value pairs in a one-to-one correspondence.