Skip to main content

PostgreSQL JSONB vs Array Column

When you need to store a list of values in a single column, PostgreSQL gives you two options: a native array (text[]) or a jsonb column. They overlap in capability but serve different needs.

The Scenario​

You're adding a profile_links field to a User model. Each user can have multiple URLs pointing to their portfolio, LinkedIn, personal site, and so on. The question: text[] or jsonb?

text[] Array​

Use an array column when the data is a flat list of the same type and the structure won't change.

# migration
add_column :users, :profile_links, :text, array: true, default: []
add_index :users, :profile_links, using: :gin

Querying is straightforward:

# find users who have a specific URL
User.where("'https://example.com/portfolio' = ANY(profile_links)")

# find users who have any of these URLs
User.where("profile_links && ARRAY[?]::text[]", ["https://linkedin.com/in/darr", "https://darr.dev"])

Array columns are simpler, use less storage, and have slightly faster reads. The GIN index makes containment queries efficient.

The limitation: every element must be the same type, and you can't attach metadata to individual items.

jsonb​

Use jsonb when each entry is a structured object, or when you expect to add fields to each item later.

# migration
add_column :users, :profile_links, :jsonb, default: []
add_index :users, :profile_links, using: :gin

Each link can carry metadata:

user.profile_links = [
  { url: "https://linkedin.com/in/darr", label: "LinkedIn", pinned: true },
  { url: "https://darr.dev",             label: "Portfolio" }
]

Querying with containment:

# find users with a specific URL
User.where("profile_links @> '[{\"url\": \"https://darr.dev\"}]'")

# find users with any pinned link
User.where("profile_links @> '[{\"pinned\": true}]'")

jsonb supports GIN indexing and a richer set of operators, but the query syntax is more verbose and write overhead is slightly higher due to binary serialization.

Which One to Pick​

Use text[] if you're storing plain URLs with no per-link metadata. The query syntax is cleaner and you're not over-engineering.

Use jsonb if you have a concrete requirement to store label, created_at, or other attributes alongside each URL. Migrating from text[] to jsonb later is doable but requires a data transformation, so it's worth making the call upfront if the requirement is clear.

If the list can grow large (hundreds of items per user), consider a separate links table instead. Storing large arrays in a single column can cause performance issues with PostgreSQL's TOAST storage.

References​