How to Fix 'function jsonb_agg does not exist' in PostgreSQL Without an Extension

Threat/Impact Level: MEDIUM | Downtime Risk: HIGH | Time to Fix: 5 mins

---

TL;DR

• What broke: Your query calls jsonb_agg() but PostgreSQL cannot resolve the function — either the search_path is wrong, you're on PG < 9.4, or a custom schema is shadowing the built-in.
• How to fix it: Qualify the function as pg_catalog.jsonb_agg(), verify your PostgreSQL version is ≥ 9.4, and audit your search_path.
• Shortcut: Use our Client-Side Sandbox below to auto-refactor this — paste your failing query and get corrected SQL instantly.

---

The Incident (What Does the Error Mean?)

Raw error output from psql or your application logs:

ERROR:  function jsonb_agg(record) does not exist
LINE 3:   SELECT jsonb_agg(t) FROM my_table t;
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

jsonb_agg() is a native built-in aggregate function introduced in PostgreSQL 9.4. It lives in pg_catalog and requires zero extension installation. When this error fires in production it means one of three things:

1. You are running PostgreSQL ≤ 9.3 — the function literally does not exist.
2. Your search_path is corrupted or stripped — the session cannot see pg_catalog.
3. A rogue schema or extension has dropped/replaced the function signature — rare but catastrophic in shared RDS/Aurora environments.

Immediate consequence: every query, ORM call, or stored procedure relying on jsonb_agg() returns a hard error. If this is in a critical reporting path or an API aggregation layer, you have a full feature outage until resolved.

---

The Attack Vector / Blast Radius

This is not a security exploit vector, but the blast radius is wide:

• ORMs (SQLAlchemy, ActiveRecord, Hibernate) that auto-generate JSON aggregation queries will throw 500s application-wide.
• Stored procedures and views that embed jsonb_agg() silently break — they compile at definition time but fail at runtime, making this hard to catch in staging.
• Cascading failures in microservices: If your API gateway depends on a PostgreSQL function that returns aggregated JSONB, a single bad search_path in a connection pool can poison every connection in that pool, causing a thundering herd of errors until the pool is recycled.
• RDS/Aurora Serverless v1 is especially prone — version upgrades between minor PG releases have been observed to corrupt custom search_path settings in parameter groups, silently removing pg_catalog from the path.

---

How to Fix It (The Solution)

Step 1: Verify Your PostgreSQL Version

SELECT version();

If the output shows PostgreSQL 9.3 or earlier, jsonb_agg does not exist. You must upgrade or use the workaround in Step 3.

Step 2: Check and Repair search_path

-- Check current path
SHOW search_path;

-- Fix for the current session
SET search_path = "$user", public, pg_catalog;

-- Fix permanently for the role
ALTER ROLE your_app_user SET search_path = "$user", public, pg_catalog;

-- Fix at database level
ALTER DATABASE your_db SET search_path = "$user", public, pg_catalog;

Basic Fix — Fully Qualify the Function

- SELECT jsonb_agg(t) FROM my_table t;
+ SELECT pg_catalog.jsonb_agg(t) FROM my_table t;

This bypasses search_path resolution entirely. Use this as an emergency patch in production.

Enterprise Best Practice — Explicit Schema + Type Cast

- SELECT jsonb_agg(row_to_json(t))
-   FROM my_table t
-   GROUP BY t.category_id;
+ SELECT pg_catalog.jsonb_agg(
+     pg_catalog.row_to_json(t)::jsonb
+   )
+   FROM my_table t
+   GROUP BY t.category_id;

Explicitly casting row_to_json() output to ::jsonb before passing to jsonb_agg() eliminates ambiguous type resolution — the most common cause of the No function matches the given name and argument types hint.

Fallback for PostgreSQL 9.3 and Below

- SELECT jsonb_agg(t) FROM my_table t;
+ SELECT CAST(array_to_json(array_agg(row_to_json(t))) AS text)
+   FROM my_table t;

This is not a drop-in replacement for JSONB semantics but unblocks you on legacy versions.

---

💡 Tired of pasting proprietary configs into ChatGPT? Generic AI tools log your company's ARNs, DB strings, and private keys. StackEngine is a zero-backend, pure Client-Side WASM utility. Drop your failing config into the sandbox above. We redact your secrets locally in the browser and auto-generate the refactored code using your own API key.

---

Prevention in CI/CD

1. Pin PostgreSQL Version in Your Pipeline

# docker-compose.yml (CI service)
services:
  postgres:
    image: postgres:16.3  # Never use 'latest'

Version drift between dev (postgres:latest) and production (RDS PG 9.3) is the #1 cause of this class of error reaching prod.

2. Add a Migration Pre-flight Check

Add this as a required CI step before any migration runs:

#!/bin/bash
# check_pg_version.sh
REQUIRED=90400  # PG 9.4
ACTUAL=$(psql $DATABASE_URL -tAc "SELECT current_setting('server_version_num')::int")
if [ "$ACTUAL" -lt "$REQUIRED" ]; then
  echo "ERROR: PostgreSQL $ACTUAL < 90400. jsonb_agg not available."
  exit 1
fi

3. Lint SQL with sqlfluff + Custom Rule

# .sqlfluff
[sqlfluff]
dialect = postgres

[sqlfluff:rules:convention.not_equal]
preferred_not_equal_style = c_style

Pair with a grep-based gate in your CI to catch unqualified built-in calls:

# Fail CI if any .sql file uses jsonb_agg without pg_catalog qualification
grep -rn --include="*.sql" 'jsonb_agg' ./migrations | grep -v 'pg_catalog' && exit 1 || exit 0

4. OPA Policy for RDS Parameter Group Drift (Terraform)

# opa/policies/rds_search_path.rego
package rds

deny[msg] {
  resource := input.resource_changes[_]
  resource.type == "aws_db_parameter_group"
  params := resource.change.after.parameter
  p := params[_]
  p.name == "search_path"
  not contains(p.value, "pg_catalog")
  msg := sprintf("RDS parameter group '%v' sets search_path without pg_catalog", [resource.address])
}

Run this in your Terraform plan pipeline via conftest to block any parameter group change that strips pg_catalog from search_path.