PostgreSQL JSON Functions & Operators: Complete Guide

PostgreSQL’s JSON and JSONB support is powerful but has a learning curve. The difference between -> and ->>, knowing which operator to use, understanding path queries - these details matter.

This guide covers the most practical JSON functions and shows real-world use cases. You’ll learn to extract, filter, and manipulate JSON data efficiently.

What You’ll Learn

This guide covers:

  • JSON extraction operators (->, ->>, #>, #>>)
  • Checking JSON structure (keys, contains)
  • Aggregation with JSON (json_agg, json_object_agg)
  • JSON path queries
  • Type conversion with -> vs ->
  • Performance optimization for JSON queries
  • Common JSON patterns and anti-patterns

JSON vs JSONB: Quick Review

JSON: Text-based, preserves whitespace, slower queries JSONB: Binary format, optimized queries, supports indexing

Always use JSONB unless you have a specific reason to preserve formatting.

Extraction Operators

The -> Operator (Returns JSON)

Returns the value as JSON type (preserving structure).

CREATE TABLE employee_settings (
    emp_id INTEGER,
    preferences JSONB
);

INSERT INTO employee_settings VALUES
(1, '{"theme": "dark", "notifications": true, "language": "en"}'),
(2, '{"theme": "light", "notifications": false, "language": "es"}');

-- Extract as JSON (returns the value, typed as JSON)
SELECT
    emp_id,
    preferences -> 'theme' as theme_json
FROM employee_settings;

Result:

emp_id | theme_json
-------+------------
      1 | "dark"
      2 | "light"

Note the quotes around dark and light. It’s JSON, not text.

The -» Operator (Returns Text)

Returns the value as TEXT type (unwraps JSON).

-- Extract as text (returns the plain value)
SELECT
    emp_id,
    preferences ->> 'theme' as theme_text,
    preferences ->> 'language' as language_text
FROM employee_settings;

Result:

emp_id | theme_text | language_text
-------+------------+---------------
      1 | dark       | en
      2 | light      | es

No quotes - these are plain text values you can use in string functions.

Nested Extraction with ->

Navigate through nested structures.

INSERT INTO employee_settings VALUES
(3, '{"ui": {"theme": "dark", "fontSize": 12}, "notifications": true}');

SELECT
    emp_id,
    preferences -> 'ui' -> 'theme' as nested_theme
FROM employee_settings
WHERE emp_id = 3;

Result:

emp_id | nested_theme
-------+----------------
      3 | "dark"

Each -> dives one level deeper.

Array Access with ->

Access JSON arrays by index.

INSERT INTO employee_settings VALUES
(4, '{"skills": ["Python", "SQL", "PostgreSQL"], "experience": 8}');

SELECT
    emp_id,
    preferences -> 'skills' -> 0 as first_skill,
    preferences -> 'skills' -> 1 as second_skill
FROM employee_settings
WHERE emp_id = 4;

Result:

emp_id | first_skill | second_skill
-------+-------------+--------------
      4 | "Python"    | "SQL"

Indexes are zero-based like arrays.

Path Operators: #> and #»

Extract values using a path array.

-- #> returns as JSON
SELECT
    emp_id,
    preferences #> '{ui,theme}' as theme_path
FROM employee_settings
WHERE emp_id = 3;

-- #>> returns as text
SELECT
    emp_id,
    preferences #>> '{ui,theme}' as theme_path_text
FROM employee_settings
WHERE emp_id = 3;

Result:

emp_id | theme_path
-------+------------
      3 | "dark"

emp_id | theme_path_text
-------+----------------
      3 | dark

Use #> and #>> when paths are dynamic or very deep.

Type Checking and Validation

typeof Function

Check JSON value types.

SELECT
    emp_id,
    jsonb_typeof(preferences) as json_type,
    jsonb_typeof(preferences -> 'theme') as theme_type,
    jsonb_typeof(preferences -> 'notifications') as notification_type
FROM employee_settings
LIMIT 3;

Result:

emp_id | json_type | theme_type | notification_type
-------+-----------+------------+-------------------
      1 | object    | string     | true
      2 | object    | string     | false
      3 | object    | object     | boolean

Returns: null, true, false, number, string, array, object

Contains Operators

Check if JSON contains specific keys or values.

-- ? checks if key exists
SELECT
    emp_id,
    preferences ? 'theme' as has_theme,
    preferences ? 'notifications' as has_notifications,
    preferences ? 'timezone' as has_timezone
FROM employee_settings;

Result:

emp_id | has_theme | has_notifications | has_timezone
-------+-----------+-------------------+--------------
      1 | t         | t                 | f
      2 | t         | t                 | f
      3 | t         | t                 | f

Check Array Membership

Use ? or ?| for arrays.

SELECT
    emp_id,
    (preferences -> 'skills' ->> 0) as first_skill,
    preferences -> 'skills' ?| ARRAY['Python', 'Java'] as has_python_or_java
FROM employee_settings
WHERE emp_id = 4;

Result:

emp_id | first_skill | has_python_or_java
-------+-------------+-------------------
      4 | Python      | t

Aggregation Functions

json_agg: Aggregate to Array

Collect values into a JSON array.

SELECT
    e.dept_id,
    d.dept_name,
    json_agg(e.first_name) as employee_names,
    json_agg(DISTINCT e.first_name) as unique_names,
    json_agg(json_build_object('name', e.first_name, 'salary', e.salary)) as employee_details
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY e.dept_id, d.dept_name
LIMIT 2;

Result:

dept_id | dept_name     | employee_names                    | unique_names
--------+---------------+-----------------------------------+---------------
      1 | Sales         | ["James", "Mary", "Robert"]       | ["James", "Mary", "Robert"]
      2 | Engineering   | ["David", "Jennifer", "Richard"]  | ["David", "Jennifer", "Richard"]

json_object_agg: Aggregate to Object

Create objects from aggregated values.

SELECT
    dept_id,
    json_object_agg(first_name, salary) as employee_salary_map
FROM employees
WHERE dept_id IN (1, 2)
GROUP BY dept_id;

Result:

dept_id | employee_salary_map
--------+--------------------------------------
      1 | {"James": 75000, "Mary": 78000}
      2 | {"David": 105000, "Jennifer": 102000}

Returns a JSON object with names as keys and salaries as values.

Building and Modifying JSON

json_build_object

Create JSON from columns.

SELECT
    emp_id,
    first_name,
    last_name,
    salary,
    json_build_object(
        'id', emp_id,
        'name', first_name || ' ' || last_name,
        'salary', salary,
        'department', 'Engineering'
    ) as employee_json
FROM employees
WHERE dept_id = 2
LIMIT 2;

Result:

emp_id | first_name | last_name | salary | employee_json
-------+------------+-----------+--------+----------------------------------------------
      7 | David      | Miller    | 105000 | {"id": 7, "name": "David Miller", ...}
      8 | Jennifer   | Martinez  | 102000 | {"id": 8, "name": "Jennifer Martinez", ...}

json_build_array

Create JSON arrays from columns.

SELECT
    emp_id,
    json_build_array(emp_id, first_name, last_name, salary) as employee_array
FROM employees
WHERE dept_id = 1
LIMIT 2;

Result:

emp_id | employee_array
-------+-------------------------------
      1 | [1, "James", "Wilson", 75000]
      2 | [2, "Mary", "Johnson", 78000]

Concatenating JSON Objects

Merge JSON objects with ||.

SELECT
    json_build_object('name', 'David', 'salary', 105000) ||
    json_build_object('department', 'Engineering', 'location', 'SF') as merged;

Result:

merged
-------
{"name": "David", "salary": 105000, "department": "Engineering", "location": "SF"}

Real-World JSON Patterns

Storing Document Metadata

Store flexible metadata alongside documents.

CREATE TABLE documents (
    doc_id INTEGER PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    metadata JSONB
);

INSERT INTO documents VALUES
(1, 'Project Charter', 'Strategic plan...',
 '{"author": "David Miller", "status": "approved", "tags": ["strategy", "2026"], "reviewed_by": {"date": "2026-02-01", "reviewer": "Sarah"}}');

-- Query nested reviewed_by
SELECT
    title,
    metadata ->> 'status' as status,
    metadata -> 'reviewed_by' ->> 'reviewer' as reviewed_by,
    metadata -> 'reviewed_by' ->> 'date' as review_date
FROM documents;

Result:

title              | status   | reviewed_by | review_date
--------------------+----------+-------------+-------------
Project Charter    | approved | Sarah       | 2026-02-01

Search Within JSON

Find documents with specific tags.

SELECT
    doc_id,
    title,
    metadata ->> 'status' as status,
    metadata -> 'tags' as tags
FROM documents
WHERE metadata -> 'tags' ? 'strategy';

Result:

doc_id | title              | status   | tags
--------+--------------------+----------+--------------------
      1 | Project Charter    | approved | ["strategy", "2026"]

Aggregating Nested Data

Summarize nested information.

SELECT
    first_name || ' ' || last_name as employee_name,
    (settings ->> 'theme') as preferred_theme,
    (settings -> 'working_hours' ->> 'start') as work_start
FROM employees e
LEFT JOIN json_table_example jt ON e.emp_id = jt.emp_id
GROUP BY e.emp_id, first_name, last_name;

Common Errors & Solutions

Error: “Cannot index with non-integer”

Problem:

SELECT preferences -> 5 as value
FROM employee_settings
WHERE emp_id = 1;

Using numeric index on non-array JSON.

Solution:

Use ->> for keys or ensure the JSON is an array:

SELECT preferences ->> 'theme' as theme
FROM employee_settings;

Error: “Column does not exist”

Problem:

SELECT theme
FROM employee_settings;

Trying to select extracted JSON as a column.

Solution:

Extract it in the SELECT clause:

SELECT preferences ->> 'theme' as theme
FROM employee_settings;

Error: Unexpected NULL values

Problem:

SELECT preferences -> 'nonexistent' as value
FROM employee_settings;

Returns NULL when key doesn’t exist (expected behavior).

Solution:

Use COALESCE to provide defaults:

SELECT
    COALESCE(preferences ->> 'timezone', 'UTC') as timezone
FROM employee_settings;

Performance Tips

1. Use JSONB, Not JSON

JSONB is faster for queries:

-- Good: JSONB with indexes
CREATE TABLE app_events (event_id BIGSERIAL, data JSONB);

-- Avoid: JSON (no indexing support)
CREATE TABLE app_events (event_id BIGSERIAL, data JSON);

2. Index JSONB Paths

For frequently queried paths, create GIN indexes:

CREATE INDEX idx_preferences_theme
  ON employee_settings USING GIN((preferences -> 'theme'));

-- Query using the index
SELECT * FROM employee_settings
WHERE (preferences ->> 'theme') = 'dark';

3. Extract Before Aggregating

-- Less efficient: Extract in aggregate function
SELECT json_agg(preferences -> 'theme') FROM employee_settings;

-- More efficient: Extract first, then aggregate
SELECT json_agg(preferences ->> 'theme') FROM employee_settings;

4. Use -» for String Comparisons

-- Faster: Compare as text
WHERE preferences ->> 'status' = 'active'

-- Slower: Convert after extraction
WHERE (preferences -> 'status')::text = '"active"'

FAQ

Q: When should I use JSON vs JSONB? A: Always use JSONB. JSON is only for preserving exact formatting.

Q: What’s the difference between -> and ->>? A: -> returns JSON type (with quotes), ->> returns text. Use ->> for string operations.

Q: How do I search for values within JSON arrays? A: Use ? value operator or unnest the array with jsonb_array_elements.

Q: Can I update JSON values? A: Yes, use jsonb_set and || (concatenation) operators.

Q: Is JSON faster or slower than normalized tables? A: JSON is slower for queries but faster for storage of semi-structured data. Use JSON for flexible schemas.

Q: How do I convert JSON to rows? A: Use jsonb_to_record or jsonb_array_elements to unnest.

Continue mastering JSON and advanced data types:

PostgreSQL’s JSON support is enterprise-grade. Master these functions and you can store flexible, queryable data efficiently.