0Pricing
SQL Academy · Lesson

Searching Inside Arrays

ANY, contains and overlap operators.

Searching Inside Arrays is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Arrays Store Multiple Values

In PostgreSQL, an array column holds multiple values in a single cell. Before you can search inside arrays, it helps to see what they look like.

The query below creates a table where each product can belong to several categories stored as a text array.

CREATE TABLE products (
  id      SERIAL PRIMARY KEY,
  name    TEXT,
  tags    TEXT[]
);

INSERT INTO products (name, tags) VALUES
  ('Laptop',     ARRAY['electronics', 'computers', 'sale']),
  ('T-Shirt',    ARRAY['clothing', 'summer', 'sale']),
  ('Coffee Mug', ARRAY['kitchen', 'gifts']),
  ('Headphones', ARRAY['electronics', 'audio']),
  ('Notebook',   ARRAY['stationery', 'office']);

Checking Membership with ANY

The ANY operator lets you test whether a value appears anywhere in an array. The syntax is: value = ANY(array_column).

This query finds every product tagged with electronics.

SELECT name, tags
FROM products
WHERE 'electronics' = ANY(tags);

ANY Is Not Just for Text

ANY works with any data type that supports equality. Here is a table of scores stored as integer arrays. The query retrieves students who achieved a perfect score of 100 in at least one test.

CREATE TABLE students (
  student TEXT,
  scores  INT[]
);

INSERT INTO students VALUES
  ('Alice', ARRAY[88, 95, 100]),
  ('Bob',   ARRAY[70, 80, 90]),
  ('Carol', ARRAY[100, 100, 99]);

SELECT student, scores
FROM students
WHERE 100 = ANY(scores);

Excluding Rows with NOT + ANY

To find rows where a value does not appear in an array, combine NOT with ANY. Note that value <> ANY(arr) has a different meaning — it returns true if any element differs, which is almost always true.

Use NOT (value = ANY(arr)) for a reliable exclusion check.

-- Find products that are NOT tagged as 'sale'
SELECT name, tags
FROM products
WHERE NOT ('sale' = ANY(tags));

The Contains Operator @>

The @> operator checks whether one array contains all elements of another. Think of it as: does the left array include every element listed on the right?

This finds products that have both the electronics tag and the sale tag.

SELECT name, tags
FROM products
WHERE tags @> ARRAY['electronics', 'sale'];

The Contained-By Operator <@

The <@ operator is the reverse of @>. It checks whether the left array is entirely contained within the right array.

This query finds products whose entire tag list is a subset of the set electronics, computers, sale.

SELECT name, tags
FROM products
WHERE tags <@ ARRAY['electronics', 'computers', 'sale'];

The Overlap Operator &&

The && operator returns true when two arrays share at least one common element. It is the array equivalent of asking: do these two lists have anything in common?

The query below finds products that share at least one tag with a given shopping list.

SELECT name, tags
FROM products
WHERE tags && ARRAY['audio', 'summer', 'gifts'];

Overlap vs Contains — Key Difference

It is easy to mix up @> and &&. Here is a clear comparison:

  • tags @> ARRAY['a','b'] — the row must have both 'a' and 'b'.
  • tags && ARRAY['a','b'] — the row needs at least one of 'a' or 'b'.
-- Contains: must have BOTH 'electronics' AND 'sale'
SELECT name FROM products WHERE tags @> ARRAY['electronics', 'sale'];

-- Overlap: must have AT LEAST ONE of 'electronics' or 'sale'
SELECT name FROM products WHERE tags && ARRAY['electronics', 'sale'];

Using array_length to Filter by Size

Sometimes you want to find rows where the array has a certain number of elements. array_length(arr, 1) returns the number of elements in the first dimension.

This query finds products with exactly three tags.

SELECT name, tags
FROM products
WHERE array_length(tags, 1) = 3;

Combining Array Searches with Other Conditions

Array operators work alongside regular SQL conditions. You can combine them with AND, OR, and any other WHERE clause predicates to build precise filters.

-- Products tagged 'sale' AND whose name starts with a letter before 'N'
SELECT name, tags
FROM products
WHERE 'sale' = ANY(tags)
  AND name < 'N'
ORDER BY name;

Indexing Arrays for Speed

For large tables, array searches can be slow without an index. PostgreSQL's GIN (Generalized Inverted Index) index is designed for array columns and supports the @>, <@, and && operators efficiently.

ANY does not use a GIN index directly, but the overlap and contains operators do.

-- Create a GIN index on the tags array column
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- This query can now use the index
SELECT name FROM products WHERE tags @> ARRAY['electronics'];

Quick Check: Array Operators

Test your understanding of PostgreSQL array search operators.

Lesson Recap

In this lesson you learned three core ways to search inside PostgreSQL arrays:

  • = ANY(arr) — checks whether a single value exists anywhere in the array.
  • @> (contains) — checks that the array includes all specified elements.
  • && (overlap) — checks that the array shares at least one element with another array.

You also saw <@ for the reverse containment check, array_length for size-based filtering, and the GIN index for making array searches fast on large datasets. Combining these operators with standard WHERE conditions gives you precise, expressive control over multi-valued data.

Frequently asked questions

Is the “Searching Inside Arrays” lesson free?

Yes — the full text of “Searching Inside Arrays” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Searching Inside Arrays”?

ANY, contains and overlap operators. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Searching Inside Arrays” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Array Columns Basics
  2. Searching Inside Arrays
  3. UNNEST and Aggregating
  4. Arrays vs Normalized Tables
← Back to SQL Academy