How to Query JSONB in PostgreSQL: Syntax and Examples

In PostgreSQL, you can query jsonb data using operators like -> and ->> to access JSON objects and values, and functions like jsonb_extract_path_text() for nested data. Use these to filter or select JSON fields directly in SQL queries.

Syntax

PostgreSQL provides special operators and functions to query jsonb columns:

• ->: Get JSON object field by key (returns JSON)
• ->>: Get JSON object field as text
• #>: Get JSON object at specified path (returns JSON)
• #>>: Get JSON object at path as text
• jsonb_extract_path_text(): Extract text at nested path

These allow you to access and filter JSON data inside your table columns.

sql

SELECT jsonb_column->'key' AS json_value, jsonb_column->>'key' AS text_value FROM table_name WHERE jsonb_column->>'key' = 'value';

Example

This example shows how to create a table with a jsonb column, insert data, and query nested JSON fields.

sql

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

INSERT INTO products (info) VALUES
  ('{"name": "Pen", "price": 1.5, "tags": ["stationery", "writing"]}'),
  ('{"name": "Notebook", "price": 3.0, "tags": ["stationery", "paper"]}');

-- Query to get product names and filter by tag
SELECT info->>'name' AS product_name
FROM products
WHERE info->'tags' ? 'writing';

Output

 product_name 
--------------
 Pen
(1 row)

Common Pitfalls

Common mistakes when querying jsonb include:

• Using -> when you want text, which returns JSON instead of plain text.
• Not using the containment operator @> to check if JSON contains a key-value pair.
• Forgetting to cast JSON text to the correct type when filtering numeric values.

Always choose the right operator for your goal and cast types when needed.

sql

/* Wrong: comparing JSON to text directly */
SELECT * FROM products WHERE info->'price' = '1.5';

/* Right: use ->> to get text and cast to numeric */
SELECT * FROM products WHERE (info->>'price')::numeric = 1.5;

Quick Reference

Operator/Function	Description	Returns
->	Get JSON object field by key	JSON
->>	Get JSON object field as text	Text
#>	Get JSON object at path	JSON
#>>	Get JSON object at path as text	Text
@>	Check if JSON contains specified JSON	Boolean
jsonb_extract_path_text()	Extract text at nested path	Text

Key Takeaways

Use -> to get JSON objects and ->> to get text values from jsonb columns.

Use @> operator to check if jsonb contains specific key-value pairs.

Cast jsonb text values to appropriate types when filtering numeric or boolean data.

Use jsonb_extract_path_text() for extracting nested JSON text values easily.

Always test your queries to ensure correct data types and expected results.