PostgreSQL Order By JSON Field

As a database developer, I’ve frequently encountered the challenge of sorting data based on JSON fields. In this tutorial, I’ll explain everything you need to know about ordering by JSON fields in PostgreSQL, complete with practical examples.

PostgreSQL Order By JSON Field

JSON is a data type in Postgresql used for storing JSON, which is abbreviated as JavaScript Object Notation. JSON can also be stored as text, but this data type ensures that every stored value is valid according to the JSON rules.

Since version 9.2, PostgreSQL has offered robust JSON support, with even more powerful capabilities added in subsequent releases. Before sorting, let’s establish a foundation for how PostgreSQL handles JSON data.

PostgreSQL provides two JSON data types:

• json: Stores an exact copy of the input text
• jsonb: Stores data in a decomposed binary format, making it more efficient for processing but slightly slower for insertion

For sorting operations, jsonb typically performs better, especially for complex queries.

Example: Constructing the JSON object with ORDER BY

In the query below, we have to get the array elements as separate rows to order them, which is the inner query data. Then, we will reconstruct the JSON object. Let’s check the query.

SELECT
    person_id,
    name,
    jsonb_build_object(
        'fields',
        jsonb_agg(element ORDER BY element->'rank' DESC)
    )
FROM (
    SELECT
        person_id,
        name,
        jsonb_array_elements(field->'fields') AS element
    FROM person
) data
WHERE person_id = 42
GROUP BY
    person_id,
    name;

After executing the above query, I got the expected output, which is shown in the screenshot below.

Basic JSON Field Sorting in PostgreSQL

Let us execute the below query to create the Products table.

CREATE TABLE Products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    specifications JSONB
);

After executing the Products table, it was created successfully.

Now, let’s run the below insert query to insert the values to the products table.

INSERT INTO products (name, specifications) VALUES
('Smartphone X', '{"weight": 280, "color": "blue", "storage": 256}'),
('Laptop Pro', '{"weight": 1100, "color": "silver", "storage": 512}'),
('Tablet Mini', '{"weight": 400, "color": "black", "storage": 128}');

After executing the above query, I got the expected output, as shown in the screenshot below.

Example 1: Sorting by a Top-Level JSON Key

To sort products by storage capacity, we can execute the below query.

SELECT name, specifications->>'storage' AS storage 
FROM products 
ORDER BY (specifications->>'storage')::integer DESC;

This query:

1. Extracts the ‘storage’ value using the ->>' operator
2. Casts the string result to integer with ::integer
3. Orders in descending order

After executing the above query, I got the expected output, as shown in the screenshot below.

Note: The -> operator returns a JSON object, while the ->> operator returns text. When ordering by numeric values, always cast the extracted text to the appropriate type.

Example 2: Sorting by Multiple JSON Fields

Sometimes, you’ll need to sort by multiple JSON fields. For example, you can sort products by weight and storage using the query below.

SELECT name, 
       specifications->>'weight' AS weight,
       specifications->>'storage' AS storage 
FROM products 
ORDER BY (specifications->>'weight')::integer ASC,
         (specifications->>'storage')::integer DESC;

After executing the above query, I got the expected output, as shown in the below screenshot.

Example-3: Using Generated Columns (PostgreSQL 12+)

For frequently sorted JSON fields, let us create the generated column using the below query.

ALTER TABLE products 
ADD COLUMN storage_capacity INTEGER GENERATED ALWAYS AS 
    ((specifications->>'storage')::integer) STORED;

After executing the above query, I got the expected output.

Now we can sort by the generated column using the below query.

SELECT name, storage_capacity
FROM products
ORDER BY storage_capacity DESC;

After executing the above query, I got the expected output, as shown in the below screenshot.

Conclusion

Sorting by JSON fields in PostgreSQL allows you to work with semi-structured data.

From my experience implementing these solutions for businesses across, I’ve found that

• For simple applications, direct JSON field extraction works well
• For performance-critical systems, generated columns or indexes are essential

Knowing these techniques allows you to create more powerful database designs that handle complex data structures.

You may also like the same topic series of articles below.

• PostgreSQL order by list of values
• PostgreSQL Order By Date
• Postgresql order by limit
• PostgreSQL Order By
• PostgreSQL Order By Group By