How to Use #> and #>> Operators in PostgreSQL JSON Queries

In PostgreSQL, the #> operator extracts a JSON object at a specified path as JSON, while the #>> operator extracts the JSON value at that path as plain text. Both are used to navigate nested JSON data by specifying the path as a text array.

Syntax

The #> and #>> operators are used to extract data from JSON or JSONB columns by specifying a path array.

• json_column #> array_of_keys: Returns the JSON object at the given path.
• json_column #>> array_of_keys: Returns the text value at the given path.

The path is an array of keys or indexes to navigate nested JSON structures.

sql

json_column #> '{key1,key2}'
json_column #>> '{key1,key2}'

Example

This example shows how to extract a nested JSON object and a text value from a JSONB column using #> and #>>.

sql

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  info JSONB
);

INSERT INTO products (info) VALUES
('{"name": "Laptop", "specs": {"cpu": "Intel", "ram": "16GB"}}'),
('{"name": "Phone", "specs": {"cpu": "ARM", "ram": "8GB"}}');

-- Extract the specs object as JSON
SELECT info #> '{specs}' AS specs_json FROM products;

-- Extract the cpu value as text
SELECT info #>> '{specs,cpu}' AS cpu_text FROM products;

Output

       specs_json       
------------------------
 {"cpu": "Intel", "ram": "16GB"}
 {"cpu": "ARM", "ram": "8GB"}

 cpu_text 
----------
 Intel
 ARM

Common Pitfalls

Common mistakes include:

• Using #>> when you want JSON output instead of text.
• Providing the path as a string instead of a text array (must use curly braces and commas).
• Trying to extract a key that does not exist, which returns null.

Always ensure the path matches the JSON structure and use the correct operator for the desired output type.

sql

SELECT info #>> '{specs}' AS wrong_usage FROM products;
-- This returns null because #>> expects a path to a text value, not an object

SELECT info #> '{specs}' AS correct_usage FROM products;
-- This returns the specs JSON object

Output

 wrong_usage 
-------------
 

       correct_usage       
------------------------
 {"cpu": "Intel", "ram": "16GB"}
 {"cpu": "ARM", "ram": "8GB"}

Quick Reference

Operator	Description	Returns
#>	Extract JSON object at path	JSON or JSONB
#>>	Extract text value at path	Text

Key Takeaways

Use #> to get JSON objects from nested JSON data by specifying a path array.

Use #>> to get plain text values from nested JSON data using a path array.

Always provide the path as a text array with curly braces and commas, e.g., '{key1,key2}'.

If the path does not exist, both operators return null without error.

Choose the operator based on whether you want JSON output (#>) or text output (#>>).