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.