PostgreSQL JSON Functions & Operators: Types, Syntax, & Usage

Want to combine a relational database’s power and the flexibility of JSON data? If so, then you’ve landed on the right page.

This article will discuss several PostgreSQL JSON functions used to query JSON data. You will also learn to employ some of the most significant JSON operators to handle JSON data more efficiently.

Let us get started!

Prerequisites

PostgreSQL must be installed appropriately & working efficiently. Basic knowledge of JSON data, including its types & usage. 

What are PostgreSQL JSON Functions?

PostgreSQL offers us a provision to create and process JSON data. Primarily there are three types of PostgreSQL JSON functions available, namely:

• Creation Functions: As the name suggests, these functions create JSON objects. For instance, “array_to_json” function converts SQL array to a JSON array. 
• Processing Functions: These functions are responsible for processing the JSON objects. For example, the “json_array_elements” function fetches the top-level JSON array and expands them into JSON values.
• Aggregate Functions: These functions compute a single-valued result from multiple inputs. For example, the “json_agg” function collects all input values & converts them into a JSON array. After that, the values are converted to JSON objects.

Read along to learn more about PostgreSQL JSON Functions in detail.

What are PostgreSQL JSON Operators?

To help you query JSON data, PostgreSQL includes two native operators: -> and ->>.

• The JSON object field is returned by key by the operator ->.
• The JSON object field is returned by text by the operator ->>.

Note: we can chain the -> operator with the operator ->> to retrieve a specific node (since the -> operator returns a JSON object).

Working with PostgreSQL JSON Functions

• Creation Functions
• Processing Functions
• Aggregate Functions

Let’s begin to understand the working of PostgreSQL JSON functions in detail, including their syntax & an example code.

Creation Functions

There are umerous PostgreSQL JSON Functions available, we’ll be discussing the most widely used functions as follows:

• to_json() function
• array_to_json() function
• row_to_json() function
• json_build_array() function
• json_build_object() function
• json_object() function, etc.

to_json() function

Any SQL value can be converted to JSON using this PostgreSQL JSON function. For instance, composites and arrays are transformed recursively into objects and arrays (multidimensional arrays become arrays of arrays in JSON).

Note: The cast function will be utilized to carry out the conversion if there is a cast from the SQL data type to JSON; a scalar JSON value is generated. The text representation will be utilized for any scalar that is not a number, a Boolean, or a null value, with any necessary escaping to convert it to a valid JSON string value.

Syntax: to_json ( anyelement )
Return Type: json
Example Code:

to_json(Pratibha said "Hi."'::text) 
-------OUTPUT--------
"Pratibha said \"Hi.\""

array_to_json() function

An SQL array is transformed into a JSON array by Array_to_json. The only difference between this behavior and to_json is that if the optional boolean parameter is true, a line will be appended between the top-level array members.

Syntax: array_to_json ( anyarray [, boolean ] )
Return Type: json
Example Code:

array_to_json('{{11,15},{98,108}}'::int[]) 
-------OUTPUT--------
[[11,15],[98,108]]

row_to_json() function

Row_to_json results in a JSON object converted from a SQL composite value. The only difference between this behavior and to_json is that if the optional boolean parameter is true, a line will be placed between the top-level components.

Syntax: row_to_json ( record [, boolean ] )
Return Type: json
Example Code:

row_to_json(row(1,'foo')) 
-------OUTPUT--------
{"f1":1,"f2":"foo"}

json_build_array() function

Constructs a potentially heterogeneously typed JSON array from a set of variadic arguments. Each argument is transformed according to to_json.

Syntax: json_build_array ( VARIADIC “any” )
Return Type: json
Example Code:

json_build_array(11, 12, 'foo', 14, 15) 
-------OUTPUT--------
[11, 12, "foo",14, 15]

json_build_object() function

Constructs a JSON object from a variadic parameter list. The argument list usually comprises keys and values that are alternated. Value arguments are translated per the to_json conversion, while key arguments are coerced to text.

Syntax: json_build_object ( VARIADIC “any” )
Return Type: json
Example Code:

json_build_object('foo', 11, 12, row(13,'bar')) 
-------OUTPUT--------
{"foo" : 11, "12" : {"f1":13,"f2":"bar"}}

json_object() function

Creates a JSON object from an array of text. Either the array has exactly one dimension with an even number of members, in which case they are treated as alternating key/value pairs, or the array has two sizes with each inner array having precisely two elements, in which case they are treated as a key/value pair. JSON strings are created from each value.

Syntax: json_object ( text[] )
Return Type: json
Example Code:

Example#1

json_object('{a, 11, b, "def", c, 13.5}') 
-------OUTPUT--------
{"a" : "11", "b" : "def", "c" : "13.5"}

Example#2
json_object('{{a, 11}, {b, "def"}, {c, 13.5}}') 
-------OUTPUT--------
{"a" : "11", "b" : "def", "c" : "13.5"}

Note: json_object() function pulls keys and values in pairs from different text arrays in the below-mentioned format. Other than that, it is the same as the one-argument form.
Syntax: json_object ( keys text[], values text[] )
Example Code:

json_object('{a,b}', '{11,12}') 
-------OUTPUT--------
{"a": "11", "b": "12"}

Processing Functions

Let’s understand the most important & widely used Processing functions available as PostgreSQL JSON Functions:

• json_array_length() function
• json_each() function
• json_each_text( ) function
• json_extract_path( ) function
• json_extract_path_text( ) function
• json_object_keys() function
• json_populate_record() function
• json_populate_recordset() function
• json_array_elements() function, etc.

json_array_length() function

Returns the size or the number of elements in the outermost JSON array.

Syntax: json_array_length(json)
Return Type: int
Example Code:

SELECT json_array_length('[11,12,13,{"f1":11,"f2":[15,16]},14]');
 json_array_length
-------OUTPUT--------
 5
(1 row)

json_each() function

The outermost JSON object is expanded into a set of key/value pairs.

Syntax: json_each(json)
Return Type: SETOF key text, value json
Example Code:

SELECT * FROM json_each('{"a":"apricot", "b":"banana"}');
-------OUTPUT--------
key | value
-----+-------
 a | "apricot"
 b | "banana"

json_each_text( ) function

The outermost JSON object is expanded into a set of key/value pairs while the returned value will be of type text.

Syntax: json_each_text(from_json json)
Return Type: SETOF key text, value json
Example Code: 

SELECT * FROM json_each_text('{"a":"car", "b":"bike"}');
-------OUTPUT--------
key | value
-----+-------
 a | car
 b | bike

json_extract_path( ) function

JSON object are returned that are pointed to bypath_elems.

Syntax: json_extract_path(from_json json, VARIADIC path_elems text[])
Return Type: json
Example Code:

SELECT json_extract_path('{"f2":{"f3":11},"f4":{"f5":58,"f6":"foo"}}','f4');
 Json_extract_path
-------OUTPUT--------
{"f5":58,"f6":"foo"}

json_extract_path_text( ) function

JSON objects are returned that are pointed to by path_elems.

Syntax: json_extract_path_text(from_json json, VARIADIC path_elems text[])
Return Type: text
Example Code:

SELECT json_extract_path_text('{"f2":{"f3":51},"f4":{"f5":59,"f6":"foo"}}','f4', 'f6');
   json_extract_path_text
-------OUTPUT--------
   foo

json_object_keys() function

A set of keys are returned in the JSON object. 

Note: Only the “outer” object will be displayed in this PostgreSQL JSON Function.

Syntax: json_object_keys(json)
Return Type: SETOF text
Example Code:

SELECT json_object_keys('{"f1":"car","f2":{"f3":"c", "f4":"a"}}');
Json_object_keys
-------OUTPUT--------
 f1
 f2

json_populate_record() function

Expands the from_json object to a row with columns matching the base’s record type specified. The best attempt will be made during the conversion; columns in a base that don’t have a corresponding key in from_json will be left empty. The most recent value is used if a column is supplied more than once.

Syntax: json_populate_record(base anyelement, from_json json, [, use_json_as_text bool=false]
Return Type: anyelement
Example Code:

SELECT * FROM json_populate_record(null::x, '{"a":11,"b":12}');
-------OUTPUT--------
 a  |  b
---+---
 11 |12

json_populate_recordset() function

A set whose columns match the record type specified by the base is created by expanding the outermost set of objects from_json. The best attempt will be made during the conversion; columns in a base that don’t have a corresponding key in from_json will be left empty. The most recent value is used if a column is supplied more than once.

Syntax: json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false]
Return Type: SETOF anyelement
Example Code:

SELECT * FROM json_populate_recordset(null::x, '[{"a":10,"b":20},{"a":30,"b":40}]');
-------OUTPUT--------
 a   | b  
---+---
 10 | 20
 30 | 40

json_array_elements() function

Expands a JSON array to a set of JSON elements.

Syntax: json_array_elements(json)
Return Type: SETOF json
Example Code:

SELECT json_array_elements('[10,true, [20,false]]');
 value
-------OUTPUT--------
 10
 true
 [20,false]

Note: The following guidelines are used sequentially to convert a JSON value to an output column’s SQL type while working with PostgreSQL JSON Functions:

• In all circumstances, a JSON null value is translated to a SQL null.
• The JSON value is simply and accurately replicated if the output column is a JSON column.
• The object’s fields are converted to columns of the output row type by recursively applying these rules if the output column is of the composite (row) type and the JSON value is a JSON object.
• The components of the JSON array are converted to elements of the output array by recursive application of these rules if the output column is an array type and the JSON value is a JSON array.
• Otherwise, if the JSON value is a string, the input conversion function for the column’s data type receives the contents of the string.
• If not, the input conversion function for the column’s data type is fed the JSON value’s regular text representation.

Aggregate Functions

Now, we understand the types and usage of Creation & Processing Functions available in PostgreSQL. Let us also learn about Aggregate Function.

• json_agg() function: gathers all input values, including null values, into a JSON array. According to to_json, values are transformed to JSON.
  Syntax: json_agg ( anyelement )
  Return type: json

• json_object_agg() function: all the key/value pairs are gathered into one JSON object. Value arguments are converted per to_json while key arguments are coerced to text. Keys cannot be null, but values can.
  Syntax: json_object_agg ( key “any”, value “any” )
  Return type: json

Check out the PostgreSQL Documentation to learn about Aggregate Functions.

Working with PostgreSQL JSON Operators

Now that we’ve learned about PostgreSQL JSON functions, lets explore the usage of PostgreSQL JSON Operators.

• JSON operator in WHERE clause
• json -> integer → json
• json -> text → json
• json ->> integer → text
• json ->> text → text
• json #> text[] → json
• json #>> text[] → text

JSON operator in WHERE clause

The WHERE clause’s JSON operators can be used to filter the returned rows. For instance, we perform the following search to determine who purchased a commodity x from the Table named orders which contains the details of all orders placed along with the customer details:

Example code: 

SELECT info ->> 'customer' AS customer
FROM orders
WHERE info -> 'items' ->> 'commodity' = 'x';

json -> integer → json

It extracts & returns the nth element of the JSON array.

Note: The elements in the array are indexed from zero, but negative integers count from the end.

Example Code:

Example#1
'[{"a":"car"},{"b":"bar"},{"c":"bat"}]'::json -> 2 → {"c":"bat"}

Example#2
'[{"a":"car"},{"b":"bar"},{"c":"bat"}]'::json -> -3 → {"a":"car"}

json -> text → json

It extracts and returns the JSON object field with the given key.

'{"a": {"b":"bat"}}'::json -> 'a' → {"b":"bat"}

json ->> integer → text

It extracts and returns the nth element of the JSON array as text.

'[11,21,31]'::json ->> 2 → 31

json ->> text → text

It extracts and returns the JSON object field with the given key as text.

'{"a":13,"b":23}'::json ->> 'b' → 23

json #> text[] → json

The path elements can either be field keys or array indexes, extracting and returning the JSON sub-object located at that path.

'{"a": {"b": ["car","bar"]}}'::json #> '{a,b,1}' → "bar"

json #>> text[] → text

It extracts and returns the JSON sub-object as the text return type at the specified path.

'{"a": {"b": ["car","bar"]}}'::json #>> '{a,b,1}' → bar

Conclusion

The most well-known PostgreSQL JSON functions, such as json_each, array_to_json, json_each_text, and json_object_keys, are implemented in this article. To grasp PostgreSQL JSON functions better, you must test each function against as many example codes as possible. Discover powerful JSON functions and operators in PostgreSQL for efficient data handling and manipulation. We sincerely hope you will benefit from the knowledge shared in this article and improve your knowledge of PostgreSQL JSON functions. 

Check out these exciting articles at Hevo:

• Working with Postgres JSON Query | Made Easy
• Work Easily With JSON Using PostgreSQL Parse JSON

Copying data into a warehouse using ETL for Data Analysis may be time-consuming if you frequently utilize PostgreSQL.

Hevo, No-Code Data Pipeline, is at your disposal to come to your aid. You can save engineering time by establishing a Data Pipeline and beginning to replicate your data from PostgreSQL to the desired warehouse using Hevo.

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to give Hevo a try? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your experience understanding the PostgreSQL JSON Functions & Operators in the comments below! We would love to hear your thoughts.