Path extraction with #> and #>> in PostgreSQL

Introduction

These operators help you get specific parts from JSON data stored in a database. They make it easy to find nested information.

You want to get a value inside a JSON column in a table.

You need to extract nested details from a JSON object or array.

You want to read JSON data without converting it to text first.

You are working with APIs that store data as JSON in your database.

Syntax

PostgreSQL

json_column #> '{path,to,key}'
json_column #>> '{path,to,key}'

#> returns the JSON object or array at the path.

#>> returns the text value at the path.

Examples

Gets the JSON value of the city inside the address object.

PostgreSQL

SELECT data #> '{address,city}' FROM users;

Gets the city as plain text from the JSON data.

PostgreSQL

SELECT data #>> '{address,city}' FROM users;

Gets the first phone number object from the phones array.

PostgreSQL

SELECT data #> '{phones,0}' FROM users;

Sample Program

This example creates a table with JSON data about users. It shows how to get the city from the address as JSON and text, and how to get the first phone number from the phones array.

PostgreSQL

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

INSERT INTO users (data) VALUES
('{"name": "Alice", "address": {"city": "Wonderland", "zip": "12345"}, "phones": ["123-4567", "987-6543"]}'),
('{"name": "Bob", "address": {"city": "Builderland", "zip": "54321"}, "phones": ["555-0000"]}');

-- Extract city as JSON
SELECT id, data #> '{address,city}' AS city_json FROM users ORDER BY id;

-- Extract city as text
SELECT id, data #>> '{address,city}' AS city_text FROM users ORDER BY id;

-- Extract first phone number as JSON
SELECT id, data #> '{phones,0}' AS first_phone_json FROM users ORDER BY id;

Important Notes

Use curly braces with commas to specify the path inside the JSON.

#> keeps the JSON format, #>> converts the result to plain text.

Array indexes start at 0 when accessing JSON arrays.

Summary

#> extracts JSON data at a path.

#>> extracts text at a path.

Both help read nested JSON easily in PostgreSQL.