SQL/JSON Data Model and JSON in SQL: A PostgreSQL Perspective

Written by Warda Bibi

The SQL:2023 standard marks an important shift in database technology. This ninth revision (ISO/IEC 9075:2023) extends SQL's capabilities significantly, particularly in how relational databases handle JSON data.

For decades, database developers faced a choice: use relational databases with their consistency and structure or sacrifice some reliability for the flexibility of document stores. SQL:2023 addresses this dilemma by enhancing JSON support within traditional SQL databases.

This article examines the JSON data model defined by SQL:2023 and explores how PostgreSQL has implemented these features. We'll look at the practical benefits these changes bring to real-world database applications and how they might affect your development practices.

If you want to learn more about how to work with the JSON data types in PostgreSQL, check out these resources:

- How to Query JSONB in PostgreSQL

- How to Query JSON Metadata in PostgreSQL

- How to Index JSON Data in PostgreSQL

What Is JSON?

JSON (JavaScript Object Notation) is an open standard format that structures data using key/value pairs and arrays, as defined in RFC 7159. It is widely used for data exchange, document storage, and handling unstructured data in web services. JSON enables flexibility in storing dynamically structured data and reduces the complexity of schema changes.

SQL/JSON Data Model: The SQL:2023 Standard 

The SQL:2016 standard marked the introduction of the SQL/JSON data model, providing a unified approach for managing JSON data in relational databases. It primarily included JSON formatting, functions, and path language, but JSON data was stored as strings.

With the release of SQL:2023, several enhancements to improve JSON handling, including the addition of a native JSON data type, were introduced. New functionalities in the SQL standard are introduced as optional features, each assigned a unique feature code. The following sections provide a concise overview of the key JSON-related features introduced in the new standard.

JSON data type (T801)

SQL:2023 introduced a native JSON data type, simplifying the storage and manipulation of JSON within SQL databases. The native JSON data type allows for more efficient storage, retrieval, and manipulation of JSON data within relational databases. 

Enhanced JSON data type (T802)

SQL:2023 also introduced an optional feature that allows developers to enforce unique key constraints when working with JSON data.

Syntax: JSON('...text...' WITH UNIQUE KEYS)

The syntax ensures that the JSON object does not contain duplicate keys. If duplicate keys are detected, the operation will fail, and an error will be raised. For example:

SELECT JSON('{"name": "Alice", "age": 30, "name": "Bob"}' WITH UNIQUE KEYS);

In this case, the JSON object contains a duplicate key ("name"), so the operation will fail with an error.

String-based JSON (T803)

While the native JSON data type is a significant improvement, SQL:2023 also retains support for string-based JSON. This support ensures backward compatibility with existing applications that rely on string-based JSON storage. For example, we can still store JSON as text:

CREATE TABLE example_text (     id SERIAL PRIMARY KEY,     data TEXT );

INSERT INTO example_text (data) VALUES ('{"name": "Bob", "age": 25}');

Hex integer literals: JSON path language (T840) 

The SQL/JSON path language, introduced in SQL:2016, has been further enhanced in SQL:2023 with the addition of hex integer literals. The SQL/JSON path language is based on JavaScript, which already supported hexadecimal literals, but SQL:2016 explicitly excluded them from SQL/JSON. Given the arrival of hexadecimal literals in the SQL language, this is now also optionally allowed in the SQL/JSON path language.

SQL/JSON simplified accessor (T860–T864) 

The "simplified accessor" feature allows more intuitive access to JSON values using dot notation and array indexing, similar to how structured types or arrays are handled in SQL. For instance, if a JSON column data contains:

{"user": {"orders": [150, 250, 350]}, ...}

You can extract specific elements using a more natural syntax:

SELECT t.data.user.orders[1], ... FROM my_table t;

This feature is essentially syntactic sugar, as its behavior is based on JSON_QUERY and JSON_VALUE, which have been part of SQL since SQL:2016.

SQL/JSON item methods (T865–T878) :

SQL:2023 introduced a comprehensive set of SQL/JSON item methods that provide powerful tools for working with JSON data. These methods include functions for converting JSON data to other SQL data types, performing arithmetic operations on JSON values, and manipulating JSON arrays and objects. SQL:2016 already contained a few of these, such as abs(), floor(), size(). The new set mainly focused on data type conversions.

T865: SQL/JSON item method: bigint() T866: SQL/JSON item method: boolean() T867: SQL/JSON item method: date() T868: SQL/JSON item method: decimal() T869: SQL/JSON item method: decimal() with precision and scale T870: SQL/JSON item method: integer() T871: SQL/JSON item method: number() T872: SQL/JSON item method: string() T873: SQL/JSON item method: time() T874: SQL/JSON item method: time_tz() T875: SQL/JSON item method: time precision T876: SQL/JSON item method: timestamp() T877: SQL/JSON item method: timestamp_tz() T878: SQL/JSON item method: timestamp precision

JSON comparison (T879–T882):

One of the challenges of working with JSON data in SQL has been the lack of standardized comparison operations. SQL:2023 addresses this issue with the introduction of JSON comparison functions. These functions allow for comparing JSON values, including support for deep equality checks, ordering, and pattern matching. This makes it easier to perform queries that involve filtering or sorting based on JSON data.

PostgreSQL and JSON

PostgreSQL has long been at the forefront of JSON support in relational databases, even before the SQL standard formalized it. In this section, we will compare the SQL:2023 JSON features with PostgreSQL's JSON implementation, highlighting how PostgreSQL not only meets but often exceeds the standard.

SQL:2023 JSON features supported in PostgreSQL

With the release of PostgreSQL 9.2 in 2012, the database introduced native JSON support, allowing users to store and query JSON data directly within the database. Since then, PostgreSQL has continued to enhance its JSON capabilities, adding support for JSONB (a binary format for JSON data) in PostgreSQL 9.4. As a result, PostgreSQL not only meets but also surpasses the SQL:2023 Native JSON Data Type (T801) feature.

PostgreSQL supports the enhanced JSON data type (T802) feature by implementing the WITH UNIQUE KEYS syntax through the following functions:

  • json_object

  • json

Additionally, the IS JSON predicate with WITH UNIQUE KEYS allows validation of JSON data, ensuring key uniqueness at the query level.

PostgreSQL also supports string-based JSON manipulation, aligning with the SQL:2023 string-based JSON feature (T803).

For SQL/JSON simplified accessor (T860–T864), PostgreSQL offers similar functionality but follows its own proprietary syntax instead of the SQL:2023 dot notation. It primarily relies on ->, ->>, #>, and #>> operators for accessing JSON properties.

While PostgreSQL does not implement the SQL:2023 JSON comparison functions (T879–T882) for JSON data types, it provides comparable functionality through the nonstandard JSONB type, which supports direct comparisons (=, <>) and deep equality checks.

PostgreSQL also has most of the SQL/JSON item methods (T865–T878) from SQL:2023, including:

  • Type conversion: string(), boolean(), bigint(), decimal(), integer(), number()

  • Mathematical functions: abs(), floor(), ceiling()

  • Date & time handling: date(), time(), timestamp(), time_tz(), timestamp_tz() (with precision support)

SQL:2023 JSON features not yet supported in PostgreSQL

PostgreSQL does not yet provide native support for hex literals in JSON path queries. However, if you need to handle hexadecimal values within JSON, you can implement a custom function to convert hex strings to integers.

PostgreSQL Features That Exceed SQL:2023

While SQL:2023 introduced significant JSON functionality, PostgreSQL has long been a leader in JSON support and offers features that go beyond the standard. Here’s what’s missing in SQL:2023 compared to PostgreSQL:

Native JSONB type

PostgreSQL introduced JSONB in 2014 as a native, binary JSON data type long before SQL:2023 established a similar standard. While SQL:2023 now offers a JSON data type, PostgreSQL’s JSONB remains superior with its unique optimizations and advanced indexing support, making it ideal for large or complex JSON workloads that require enhanced performance and scalability.

Advanced JSON path features

PostgreSQL supports regex, custom functions, and complex filtering in JSON path expressions, which are not part of the SQL:2023 standard.

JSONB modification functions

PostgreSQL provides functions like jsonb_set, jsonb_insert, and jsonb_delete for modifying JSONB data, which are not covered by SQL:2023, even for JSON.

JSONB aggregation functions

PostgreSQL offers jsonb_agg and jsonb_object_agg for aggregating data into JSON structures, which are not part of SQL:2023.

PL/pgSQL JSON support

PostgreSQL enables custom JSON manipulation using PL/pgSQL, allowing developers to create advanced workflows beyond the standard’s capabilities.

Conclusion

The SQL:2023 standard represents a significant step forward in integrating JSON data within relational databases. PostgreSQL, with its long history of JSON support and continuous innovation, is well-positioned to take full advantage of these new features and goes beyond the standard with several advanced JSON features.  

As JSON adoption continues to grow, JSON support will play a crucial role in enabling developers to build more dynamic and efficient applications. 

Built on PostgreSQL's solid foundation, TimescaleDB inherits this robust JSON functionality while adding specialized time-series capabilities. This powerful combination allows developers to seamlessly blend structured relational data, JSON flexibility, and real-time analytics on time-series data in a single database platform. 

For organizations working with both time-series data and JSON documents—such as IoT applications tracking device telemetry with nested JSON metadata or monitoring systems collecting structured metrics alongside unstructured event data—Timescale provides an elegant solution without compromising PostgreSQL's JSON capabilities. Try it for free by signing up for a Timescale Cloud account today or installing TimescaleDB on your machine.