PostgreSQL is much more than a relational database that stores simple values such as numbers, strings, and dates. One of its most powerful features is its support for advanced data types, including JSON, arrays, geometric data, and geographic coordinates.
In this article, we will focus on JSON and see how PostgreSQL can work directly with data stored inside a JSON document.
Working with JSON in PostgreSQL
JSON, which stands for JavaScript Object Notation, is a popular format for representing structured data.
For example, a JSON value might look like this:
{
"name": "Alice",
"age": 30
}
Instead of storing name and age as separate database columns, we can store the entire structure in a PostgreSQL column using the JSON data type.
Imagine that we have a table called json_test with a column called val:
CREATE TABLE json_test (
val JSON
);
The table could contain values such as:
{"name": "Alice", "age": 30}
{"name": "Bob", "age": 25}
The interesting part is that PostgreSQL doesn't simply treat these values as ordinary text. It understands the JSON structure and allows us to access individual properties.
Extracting Values from JSON
PostgreSQL provides special operators for working with JSON.
For example, we can use the -> operator to extract a property:
SELECT val -> 'name'
FROM json_test;
This tells PostgreSQL to take the val column, interpret it as JSON, and extract the name property.
The result would be something similar to:
"Alice"
"Bob"
There is also another very useful operator: ->>.
SELECT val ->> 'name'
FROM json_test;
The difference is important:
->returns the result as JSON.->>returns the result as text.
Therefore, if you need to work with the extracted value as a normal text value, ->> is often the appropriate choice.
Using JSON Values in a WHERE Clause
PostgreSQL can also use values inside JSON documents when filtering rows.
For example:
SELECT val
FROM json_test
WHERE val ->> 'name' = 'Alice';
Here, name is not a database column.
It is a property contained inside the JSON value stored in the val column.
PostgreSQL goes inside the JSON document, extracts the value associated with name, and compares it with the string 'Alice'.
The result will therefore contain only the JSON object belonging to Alice:
{
"name": "Alice",
"age": 30
}
This demonstrates an important feature of PostgreSQL: SQL queries can operate not only on traditional database columns but also on structured data stored inside those columns.
Why JSON Support Is Useful
The ability to query JSON directly can be extremely useful when dealing with data whose structure is flexible or changes over time.
For example, an application might receive information from an external API:
{
"name": "Alice",
"email": "alice@example.com",
"preferences": {
"language": "English",
"notifications": true
}
}
Instead of immediately transforming every property into separate relational columns, PostgreSQL can store the JSON structure and allow the application to query individual properties when necessary.
This can be particularly useful for:
API responses
Configuration data
Metadata
Semi-structured information
Data with optional properties
Applications where the structure can evolve over time
PostgreSQL Supports More Than JSON
JSON is only one example of PostgreSQL's support for advanced data types.
PostgreSQL also provides support for data types such as:
Arrays
Geometric types
Spatial and geographic data
JSON and JSONB
Dates and timestamps
Network address types
Range types
This means that PostgreSQL can represent and manipulate much more complex information than simple strings and numbers.
JSON Is More Than Just Text
One of the most important concepts to understand is that PostgreSQL can actually understand the structure of JSON data.
When we execute:
SELECT val ->> 'name'
FROM json_test;
PostgreSQL is not simply searching for the word name inside a string.
It understands that name is a property of a JSON object, accesses that property, extracts its value, and returns it to the query.
This makes JSON a powerful option when relational and semi-structured data need to coexist in the same database.
PostgreSQL's support for advanced data types is one of the features that makes it such a powerful database system.
JSON is a particularly good example because it allows developers to combine the reliability and querying capabilities of a relational database with the flexibility of a structured document format.
With operators such as and , we can easily extract information from JSON documents and even use that information in WHERE clauses.
And JSON is only the beginning. PostgreSQL provides many other specialized data types and operators that allow developers to work with complex data directly inside the database.
Understanding these capabilities is an important step toward making better use of PostgreSQL and its extensive feature set.