PostgreSQL JSON and Array Columns

Schemas and Data / Guide 10 of 47 / Intermediate

• OSUbuntu 26.04 LTS (resolute)
• PostgreSQL18.6-0ubuntu0.26.04.1
• Server timezoneEtc/UTC
• TimeAbout 15 min
• Reviewed27 August 2026

Written against the versions above. Use `jsonb`, not `json`. `json` stores the original text and reparses on every access; `jsonb` stores a parsed binary form, supports indexing and is what every operator here needs.

Before you start

• The customers table with its tags and profile columns.

1. 1

   Query inside a JSON document

   Three jsonb features in one statement.

   ->> extracts a value as text; -> would return jsonb. The distinction matters when comparing - profile->>'tier' = 'gold' compares text, and profile->'tier' = 'gold' is a type error.

   (profile->>'seats')::int casts explicitly, because JSON extraction yields text. Sorting without the cast would order 9 before 3 as strings.

   @> is containment - *does this document contain this fragment* - and it is the operator that matters most, because it is the one a GIN index can accelerate. profile->>'tier' = 'gold' reads naturally and cannot use a GIN index; profile @> '{"tier":"gold"}' can.

   Two gold customers, ordered by seats.

   $psql -d appdb -c "SELECT name, profile->>'tier' AS tier, (profile->>'seats')::int AS seats FROM customers WHERE profile @> '{\"tier\":\"gold\"}' ORDER BY seats DESC"     name      | tier | seats---------------+------+------- Zoe Washburne | gold |     9 Ada Lovelace  | gold |     3(2 rows)$psql -d appdb -c "SELECT name, tags FROM customers WHERE 'vip' = ANY(tags)"     name     |   tags--------------+---------- Ada Lovelace | {vip,eu}(1 row)

   Expected resultTwo gold-tier rows sorted by seats, then the row tagged vip.

   Success conditionYou can filter on JSON contents without extracting them in application code.

2. 2

   Query and expand an array column

   'vip' = ANY(tags) is array membership, and it reads as an English sentence.

   Then unnest does the opposite: it expands each array element into its own row, so an array column can be grouped and counted like a join table. Four tags across three customers, one row each.

   That is the honest trade with array columns. They avoid a join table for genuinely list-like data - tags, labels, permissions - and they are indexable with GIN. But there is no foreign key from an array element, so nothing guarantees a tag is spelled the same way twice. Use them where the values are opaque strings you do not need referential integrity on, and a real table where you do.

   $psql -d appdb -c "SELECT unnest(tags) AS tag, count(*) FROM customers GROUP BY 1 ORDER BY 2 DESC, 1" tag  | count------+------- apac |     1 eu   |     1 us   |     1 vip  |     1(4 rows)

   Expected resultThe vip row, then four tags with counts.

   Success conditionYou can treat an array column as rows when you need to.

Troubleshooting

• A jsonb query is slow on a large table.

  Why: No GIN index, or a query shape an index cannot serve.

  Fix:CREATE INDEX ... USING GIN (profile) and query with @>. Extraction with ->> does not use a GIN index.

• ERROR: operator does not exist: jsonb = unknown.

  Why: -> returns jsonb, ->> returns text, and they are being compared to the wrong thing.

  Fix:->> for text comparisons; cast explicitly for numbers.

• Array values are inconsistent - vip, VIP, Vip.

  Why: No constraint can enforce a vocabulary inside an array.

  Fix:That is the cost of the type. Normalise on write, or use a lookup table with a foreign key when the vocabulary matters.

• Chose json and the operators behave oddly.

  Why: json preserves the original text including whitespace and key order, and reparses on every access.

  Fix:ALTER TABLE ... ALTER COLUMN x TYPE jsonb USING x::jsonb.

Official sources

• PostgreSQL 18 Documentation - Data Types
• PostgreSQL 18 Documentation - Date/Time Types
• PostgreSQL 18 Documentation - Constraints
• PostgreSQL 18 Documentation - INSERT ... ON CONFLICT
• PostgreSQL 18 Documentation - JSON Types and Functions
• PostgreSQL 18 Documentation - Arrays