How to order by on a key inside jsonb column in postgresql?

@lew 

To order by a key inside a jsonb column in PostgreSQL, you can use the jsonb_extract_path_text() function to extract the value of a specific key and then order the results based on that value. Here's an example:

Assuming you have a table called 'data' with a jsonb column called 'json_data' that stores JSON data:

1
2
3
4
5
6
7
8
9

CREATE TABLE data (
    id SERIAL PRIMARY KEY,
    json_data JSONB
);

INSERT INTO data (json_data) VALUES
('{"name": "Alice", "age": 30}'),
('{"name": "Bob", "age": 25}'),
('{"name": "Charlie", "age": 35}');

To order the rows based on the value of the "name" key inside the json_data column, you can use the following query:

1
2
3

SELECT * 
FROM data 
ORDER BY json_data -> 'name'; 

This query extracts the value of the "name" key using the -> operator and orders the results based on that value.

You can also use the jsonb_extract_path_text() function to achieve the same result:

1
2
3

SELECT *
FROM data
ORDER BY jsonb_extract_path_text(json_data, 'name');

This function allows you to specify the path to the key you want to extract and order by.

Both of these methods will order the rows in the 'data' table based on the value of the "name" key inside the json_data column.