GIN index for arrays and JSONB in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Query output with GIN index on JSONB array

Given a PostgreSQL table products with a JSONB column tags storing arrays of tags, and a GIN index on tags, what will be the output of this query?

SELECT id FROM products WHERE tags @> '["organic"]';

Assume the table has:
- id=1, tags=['organic', 'fresh']
- id=2, tags=['imported']
- id=3, tags=['organic', 'imported']

PostgreSQL

CREATE TABLE products (id SERIAL PRIMARY KEY, tags JSONB);
INSERT INTO products (tags) VALUES
  ('["organic", "fresh"]'),
  ('["imported"]'),
  ('["organic", "imported"]');
CREATE INDEX idx_gin_tags ON products USING GIN (tags);

A [1, 3]

B [2]

C [1, 2, 3]

D []

💡 Hint

The operator @> checks if the JSONB array contains the specified element.

The query filters rows where the JSONB array tags contains the element "organic". Rows with id 1 and 3 have "organic" in their tags, so they are returned.

🧠 Conceptual

intermediate

Purpose of GIN index on JSONB columns

What is the main advantage of using a GIN index on a JSONB column that stores arrays in PostgreSQL?

A It speeds up containment queries like checking if an array contains a specific element.

B It compresses the JSONB data to save disk space.

C It automatically validates the JSONB structure for correctness.

D It converts JSONB data into relational tables for faster joins.

💡 Hint

Think about what queries involving array elements need to be fast.

GIN indexes are designed to speed up queries that check if a JSONB array contains certain elements, improving performance for containment operators.

📝 Syntax

advanced

Correct syntax to create a GIN index on a JSONB column

Which of the following SQL commands correctly creates a GIN index on the JSONB column data in the table events?

A CREATE INDEX idx_gin_data ON events USING GIN (data jsonb_path_ops);

B CREATE INDEX idx_gin_data ON events USING HASH (data);

C CREATE INDEX idx_gin_data ON events USING GIN (data);

D CREATE INDEX idx_gin_data ON events USING BTREE (data);

💡 Hint

GIN indexes require the USING GIN clause and the column name.

Option C uses the correct syntax to create a GIN index on the JSONB column. Option C is invalid syntax because jsonb_path_ops is specified incorrectly here.

❓ optimization

advanced

Optimizing queries on JSONB arrays with GIN indexes

You have a table users with a JSONB column preferences storing arrays of strings. You want to optimize queries that check if a user has a specific preference. Which index type and operator combination is best for this?

A Create a GIST index on <code>preferences</code> and use the <code>~</code> operator.

B Create a BTREE index on <code>preferences</code> and use the <code>=</code> operator.

C Create a HASH index on <code>preferences</code> and use the <code>LIKE</code> operator.

D Create a GIN index on <code>preferences</code> and use the <code>@></code> operator in queries.

💡 Hint

Think about which index supports containment queries on JSONB arrays.

GIN indexes support fast containment queries using the @> operator on JSONB arrays, making option D the best choice.

🔧 Debug

expert

Why does this GIN index query return no rows?

You created a GIN index on the JSONB column info in table documents. The column stores arrays of strings. You run this query:

SELECT * FROM documents WHERE info @> '"urgent"';

But it returns no rows, even though some rows have "urgent" in their arrays. What is the problem?

A The index was created on the wrong column type; it must be on text, not JSONB.

B The query is missing array brackets; it should be <code>@> '["urgent"]'</code> to match an array containing "urgent".

C GIN indexes do not support the <code>@></code> operator on JSONB columns.

D The query should use the <code>=</code> operator instead of <code>@></code>.

💡 Hint

Check the JSONB syntax for arrays in the query.

The @> operator expects a JSONB value on the right side. To check if an array contains "urgent", the right side must be a JSONB array: '["urgent"]'. Without brackets, it looks for a string, not an array element.

Practice

1. What is the main purpose of a GIN index in PostgreSQL when used with arrays or JSONB columns?

easy

A. To speed up searches for specific elements inside arrays or JSONB data

B. To compress the data stored in arrays or JSONB columns

C. To automatically update array or JSONB data when rows change

D. To enforce uniqueness on array or JSONB columns

Solution

1. Step 1: Understand GIN index purpose

   GIN indexes are designed to speed up searches inside complex data types like arrays and JSONB by indexing their elements.

2. Step 2: Compare options

   Options B, C, and D describe compression, automatic updates, and uniqueness enforcement, which are not the main roles of GIN indexes.

3. Final Answer:

   To speed up searches for specific elements inside arrays or JSONB data -> Option A

4. Quick Check:

   GIN index purpose = speed up element search [OK]

Hint: GIN indexes speed up element searches inside arrays/JSONB [OK]

Common Mistakes:

• Confusing GIN with data compression
• Thinking GIN enforces uniqueness
• Assuming GIN auto-updates data

2. Which of the following is the correct syntax to create a GIN index on a JSONB column named data in a table items?

easy

A. CREATE INDEX idx_data ON items USING HASH (data);

B. CREATE INDEX idx_data ON items USING GIN (data);

C. CREATE INDEX idx_data ON items USING GIN (data jsonb_path_ops);

D. CREATE INDEX idx_data ON items USING BTREE (data);

Solution

1. Step 1: Identify correct index type for JSONB

   GIN indexes are created using USING GIN and applied directly on the JSONB column.

2. Step 2: Check syntax correctness

   CREATE INDEX idx_data ON items USING GIN (data); uses correct syntax: CREATE INDEX idx_data ON items USING GIN (data); CREATE INDEX idx_data ON items USING GIN (data jsonb_path_ops); is invalid because jsonb_path_ops must be specified inside parentheses, e.g., data jsonb_path_ops is incorrect syntax here.

3. Final Answer:

   CREATE INDEX idx_data ON items USING GIN (data); -> Option B

4. Quick Check:

   Correct GIN index syntax = CREATE INDEX idx_data ON items USING GIN (data); [OK]

Hint: Use 'USING GIN (column)' to create GIN index on JSONB [OK]

Common Mistakes:

• Using BTREE or HASH instead of GIN
• Incorrect syntax with jsonb_path_ops
• Missing USING keyword

3. Given the table products with a JSONB column tags and a GIN index on tags, what will the following query return?

SELECT id FROM products WHERE tags @> '["organic"]';

medium

A. All product ids where the tags array contains the element 'organic'

B. All product ids where the tags array is exactly '["organic"]'

C. All product ids where the tags array contains any element

D. Syntax error due to incorrect JSONB operator

Solution

1. Step 1: Understand the JSONB containment operator @>

   The operator @> checks if the left JSONB contains the right JSONB. Here, it checks if tags contains the element 'organic'.

2. Step 2: Analyze the query result

   The query returns all product ids where the tags array includes 'organic' anywhere, not just exact match or any element.

3. Final Answer:

   All product ids where the tags array contains the element 'organic' -> Option A

4. Quick Check:

   tags @> '["organic"]' means contains 'organic' [OK]

Hint: Use @> to check if JSONB contains specific element [OK]

Common Mistakes:

• Thinking @> means exact match
• Confusing @> with existence of any element
• Assuming syntax error with @>

4. You created a GIN index on a JSONB column info but your queries using info @> '{"key": "value"}' are still slow. What is the most likely cause?

medium

A. GIN indexes do not support the @> operator

B. The queries are missing the WHERE clause

C. The GIN index was created without the jsonb_path_ops operator class

D. The JSONB column contains NULL values

Solution

1. Step 1: Understand GIN index operator classes

   GIN indexes on JSONB can use default or jsonb_path_ops operator class. The latter is optimized for existence queries using @>.

2. Step 2: Identify cause of slow queries

   If the index was created without jsonb_path_ops, the index may not efficiently support @> queries, causing slow performance.

3. Final Answer:

   The GIN index was created without the jsonb_path_ops operator class -> Option C

4. Quick Check:

   Missing jsonb_path_ops = slow @> queries [OK]

Hint: Use jsonb_path_ops for faster @> queries on JSONB [OK]

Common Mistakes:

• Assuming GIN doesn't support @>
• Ignoring operator class choice
• Blaming NULL values for index slowness

5. You want to create a GIN index on a table orders with a column items that stores an array of integers. Which statement correctly creates the index and optimizes queries checking if an integer is present in the array?

hard

A. CREATE INDEX idx_items_gin ON orders USING GIN (items gin_int_ops);

B. CREATE INDEX idx_items_gin ON orders USING GIN (items gin__int_ops);

C. CREATE INDEX idx_items_gin ON orders USING GIN (items gin__intarray_ops);

D. CREATE INDEX idx_items_gin ON orders USING GIN (items);

Solution

1. Step 1: Identify correct GIN index syntax for integer arrays

   For integer arrays, the default GIN index supports containment and membership queries without specifying operator classes.

2. Step 2: Validate options

   Options B, C, and D use invalid operator class names like gin__int_ops or gin__intarray_ops, which do not exist in PostgreSQL.

3. Final Answer:

   CREATE INDEX idx_items_gin ON orders USING GIN (items); -> Option D

4. Quick Check:

   Default GIN index on array column = CREATE INDEX idx_items_gin ON orders USING GIN (items); [OK]

Hint: Use default GIN index on array column without extra ops [OK]

Common Mistakes:

• Using non-existent operator classes
• Adding unnecessary syntax after column name
• Confusing GIN with other index types