Intermediate9 min read PostgreSQL

Arrays, STRING_AGG & JSON — Many Rows into One Cell

Collapse a group into a delimited string, a real array, or a JSON document — and turn it back into rows again when you need to.

STRING_AGG
ARRAY_AGG
UNNEST
ANY
JSON_AGG
JSON_BUILD_OBJECT
STRING_TO_ARRAY

Use this when you need to:

Put every course a student is taking this term in one comma-separated cell for the advisor's roster
List each student's active advisors by name — without a row per advisor
Filter to students whose schedule contains a specific course, using the array itself
Emit one JSON document per student, with a nested array of courses, for a web API or a Power BI hand-off

The most common reporting question is "show me one row per student with all their X". SQL's answer is an aggregate that concatenates instead of adding: STRING_AGG for text, ARRAY_AGG for an array you can still query, JSON_AGG for a nested document. Each one accepts ORDER BY inside the parentheses, and each one accepts FILTER and DISTINCT like any other aggregate.

STRING_AGG — a delimited list

SELECT
  scs_student,
  COUNT(*)                                                   AS courses,
  STRING_AGG(scs_course_name, ', ' ORDER BY scs_course_name) AS course_list
FROM ods_stu_course_sec
WHERE scs_term = '2026FA'
GROUP BY scs_student
ORDER BY scs_student
LIMIT 10;

ORDER BY, DISTINCT and FILTER inside the aggregate

-- Advisor names live on ods_person; the assignment table carries the active flag
SELECT
  aa.saa_student,
  COUNT(*) AS assignments_ever,
  STRING_AGG(p.last_name || ', ' || p.first_name, '; ' ORDER BY aa.saa_start_date)
    FILTER (WHERE aa.saa_is_active = 'Y')                   AS active_advisors,
  STRING_AGG(DISTINCT aa.advisor_type_desc, ' / ')          AS advisor_types
FROM ods_advisor_assignments aa
  JOIN ods_person p ON p.id = aa.saa_advisor
GROUP BY aa.saa_student
HAVING COUNT(*) > 1
ORDER BY aa.saa_student;

STRING_AGG(DISTINCT …) cannot also take an ORDER BY on a different column — PostgreSQL requires the ORDER BY expression to match the DISTINCT one. Choose one or aggregate in a CTE first. And remember a NULL value is skipped, so a student whose only advisor row is inactive gets NULL, not an empty string.

ARRAY_AGG — keep it queryable

A string is the end of the road; an array is not. You can ask how long it is (CARDINALITY), index into it (courses[1]), and filter on membership with = ANY (…). That last one is how you find "students taking BIOL-101" from the already-aggregated schedule without joining back to the detail table.

WITH schedules AS (
  SELECT
    scs_student,
    ARRAY_AGG(scs_course_name ORDER BY scs_course_name) AS courses
  FROM ods_stu_course_sec
  WHERE scs_term = '2026FA'
  GROUP BY scs_student
)
SELECT
  scs_student,
  courses,
  CARDINALITY(courses) AS course_count,
  courses[1]           AS first_course
FROM schedules
WHERE 'BIOL-101' = ANY (courses)
ORDER BY scs_student;

UNNEST — back to rows

WITH schedules AS (
  SELECT scs_student, ARRAY_AGG(scs_course_name ORDER BY scs_course_name) AS courses
  FROM ods_stu_course_sec
  WHERE scs_term = '2026FA'
  GROUP BY scs_student
)
SELECT s.scs_student, c.course, c.position
FROM schedules s
  CROSS JOIN LATERAL UNNEST(s.courses) WITH ORDINALITY AS c(course, position)
ORDER BY s.scs_student, c.position
LIMIT 12;

STRING_TO_ARRAY — split a coded value

-- 'BIOL-101' → subject and number. SPLIT_PART does the same for one piece.
SELECT DISTINCT
  scs_course_name,
  (STRING_TO_ARRAY(scs_course_name, '-'))[1] AS subject,
  (STRING_TO_ARRAY(scs_course_name, '-'))[2] AS course_number,
  SPLIT_PART(scs_course_name, '-', 2)        AS same_thing_with_split_part
FROM ods_stu_course_sec
ORDER BY scs_course_name
LIMIT 8;

JSON — one nested document per parent

SELECT
  scs_student,
  JSON_AGG(
    JSON_BUILD_OBJECT(
      'course',  scs_course_name,
      'credits', scs_credits,
      'grade',   scs_verified_grade
    ) ORDER BY scs_course_name
  ) AS courses_json
FROM ods_stu_course_sec
WHERE scs_term = '2026SP'
GROUP BY scs_student
ORDER BY scs_student
LIMIT 3;

Reading JSON back

-- TO_JSONB turns a whole row into a document; ->> pulls a field out as text, -> as JSON
WITH doc AS (
  SELECT TO_JSONB(t) AS term_doc
  FROM ods_terms t
  WHERE t.terms_id = '2026FA'
)
SELECT
  term_doc ->> 'term_desc'                    AS term_desc,
  (term_doc ->> 'term_start_date')::date      AS starts,
  term_doc -> 'term_reporting_year'           AS reporting_year_as_json,
  JSONB_PRETTY(term_doc)                      AS whole_document
FROM doc;

Prefer jsonb over json for anything you store or index: it is parsed once, supports indexing and the containment operators, and deduplicates keys. json preserves the exact text and key order, which only matters if a consumer will diff the output.

Practice challenge

One row per advisor (last name, first name) showing how many active advisees they have and a comma-separated, sorted list of the advisee IDs. Busiest advisor first.

Solution

SELECT
  p.last_name || ', ' || p.first_name                              AS advisor,
  COUNT(*)                                                         AS active_advisees,
  STRING_AGG(aa.saa_student, ', ' ORDER BY aa.saa_student)         AS advisee_ids
FROM ods_advisor_assignments aa
  JOIN ods_person p ON p.id = aa.saa_advisor
WHERE aa.saa_is_active = 'Y'
GROUP BY p.last_name, p.first_name
ORDER BY active_advisees DESC, advisor;

Practise this on real university data

Write real PostgreSQL in your browser and get it graded on the rows it returns. No signup and nothing to install for your first query.

Run your first query Browse practice problems

See all 21 lessons