Arrow operators (-> and ->>) in PostgreSQL

Introduction

Arrow operators help you get data from JSON columns easily. They let you pick parts of the JSON without extra work.

You have a JSON column in your table and want to get a specific key's value.

You want to extract a JSON object or array from a JSON column.

You need to get a text value from a JSON column for filtering or display.

You want to work with nested JSON data inside your database.

Syntax

PostgreSQL

json_column -> 'key'
json_column ->> 'key'

-> returns a JSON object or array.

->> returns plain text (string) from the JSON value.

Examples

Gets the JSON value for key 'name' from the 'data' JSON column.

PostgreSQL

SELECT data -> 'name' FROM users;

Gets the text value for key 'name' from the 'data' JSON column.

PostgreSQL

SELECT data ->> 'name' FROM users;

Gets the text value of 'city' inside the nested 'address' JSON object.

PostgreSQL

SELECT data -> 'address' ->> 'city' FROM users;

Sample Program

This example creates a table with a JSON column, inserts two users with nested JSON data, then shows how to get the 'address' JSON object and the 'city' text inside it.

PostgreSQL

CREATE TABLE users (id SERIAL PRIMARY KEY, data JSON);

INSERT INTO users (data) VALUES
  ('{"name": "Alice", "age": 30, "address": {"city": "Paris", "zip": "75000"}}'),
  ('{"name": "Bob", "age": 25, "address": {"city": "Berlin", "zip": "10115"}}');

-- Get the JSON object for 'address'
SELECT data -> 'address' AS address_json FROM users;

-- Get the city as text from the nested address
SELECT data -> 'address' ->> 'city' AS city FROM users;

Important Notes

Use -> when you want to keep the JSON format for further JSON operations.

Use ->> when you want plain text to use in comparisons or display.

If the key does not exist, both operators return NULL.

Summary

Arrow operators help extract data from JSON columns easily.

-> returns JSON; ->> returns text.

They work well for nested JSON data too.