Neon is Generally Available! Serverless Postgres with branching to boost your development velocity.Read more
Neon
...Log InSign Up
Community

Understanding the new JSON capabilities in Postgres 16

Learn about the latest new features introduced in Postgres 16 for working with JSON data

We’re excited to announce that Neon now supports Postgres 16. This latest release includes several performance improvements and developer experience enhancements. One of the most anticipated features is the expanded support for SQL/JSON syntax, including:

  • JSON_ARRAY(): Constructs a JSON array.
  • JSON_ARRAYAGG(): Aggregates input values into a JSON array.
  • IS JSON: A predicate to determine if a given value is a valid JSON.

In this article, we will cover these new functions and predicates that you can try out on Neon today.

JSON_ARRAY

The json_array() function is designed for constructing a JSON array. There are two main usages:

From a series of values:

  SELECT json_array(1, true, json '{"a":null}');

Output:

      json_array       
-----------------------
 [1, true, {"a":null}]

From the outcome of a SELECT query:

SELECT json_array(SELECT * FROM (VALUES(1),(2)) t);

Output:

 json_array 
------------
 [1, 2]
(1 row)

JSON_ARRAYAGG

The json_arrayagg() function essentially behaves like the json_array() function but is designed to operate as an aggregate function. To understand the difference between json_arrayagg()  and json_array() functions, let’s consider the following example.

-- Creating the users table
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    email VARCHAR(255)
);

-- Inserting values into the users table
INSERT INTO users (user_id, first_name, last_name, email) VALUES
(1, 'John', 'Doe', 'john@email.com'),
(2, 'Jane', NULL, 'jane@email.com'),
(3, 'Bob', 'Smith', NULL);

Using json_array(), the query will return a list of arrays that include the last names: 

SELECT json_array(last_name) FROM users;

Output:

 json_array 
------------
 ["Doe"]
 []
 ["Smith"]
(3 rows)

However, with json_arrayagg(), the query returns one array of all last names:

SELECT json_arrayagg(last_name) FROM users;

Output:

  json_arrayagg   
------------------
 ["Doe", "Smith"]

IS JSON Predicate

The IS JSON predicate is introduced to test if an expression can be parsed as JSON and possibly of a specified type.

Testing various JSON types:

Example:

 SELECT js,
    js IS JSON "json?",
    js IS JSON SCALAR "scalar?",
    js IS JSON OBJECT "object?",
    js IS JSON ARRAY "array?"
  FROM (VALUES ('123'), ('"abc"'), ('{"a": "b"}'), ('[1,2]'), ('abc')) foo(js);

Output:

     js     | json? | scalar? | object? | array? 
------------+-------+---------+---------+--------
 123        | t     | t       | f       | f
 "abc"      | t     | t       | f       | f
 {"a": "b"} | t     | f       | t       | f
 [1,2]      | t     | f       | f       | t
 abc        | f     | f       | f       | f
(5 rows)

Testing arrays with unique keys:

Example:

  SELECT js,
    js IS JSON OBJECT "object?",
    js IS JSON ARRAY "array?",
    js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
    js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
  FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]')) foo(js);

Output:

             js               | object? | array? | array w. UK? | array w/o UK? 
--------------------------------+---------+--------+--------------+---------------
 [{"a":"1"}, {"b":"2","b":"3"}] | f       | t      | f            | t
(1 row)

Conclusion

Postgres 16 introduces many improvements and features, some of which were contributed by the Neon Postgres team (Heikki Linnakangas, Matthias van de Meent, Tristan Partin). We’re excited about this new version, which you can try it out on Neon today.

If you have any questions or feedback, please reach out to us in our community forum. We’d love to hear from you.

Also, make sure to subscribe below if you would like to be notified of new content we publish on our blog.

More from Neon