Indexing JSONB with GIN in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a GIN index on the JSONB column named 'data'.

PostgreSQL

CREATE INDEX idx_data ON my_table USING [1] (data);

Drag options to blanks, or click blank then click option'

Abtree

Bgin

Chash

Dgist

Attempts:

3 left

💡 Hint

Common Mistakes

Using btree index which does not support JSONB efficiently.

Using hash index which is not suitable for JSONB.

Using gist index which is less common for JSONB.

The GIN index type is used for indexing JSONB columns efficiently in PostgreSQL.

2fill in blank

medium

Complete the code to create a GIN index on the 'info' JSONB column with the jsonb_path_ops operator class.

PostgreSQL

CREATE INDEX idx_info ON users USING gin (info [1]);

Drag options to blanks, or click blank then click option'

Ajsonb_path_ops

Bjsonb_ops

Cjson_ops

Djson_path_ops

Attempts:

3 left

💡 Hint

Common Mistakes

Using jsonb_ops which is the default but larger index.

Using json_ops which is invalid for JSONB.

Using json_path_ops which is not a valid operator class.

The jsonb_path_ops operator class creates a smaller and faster GIN index for existence queries on JSONB.

3fill in blank

hard

Fix the error in the index creation statement to correctly use GIN on the JSONB column 'attributes'.

PostgreSQL

CREATE INDEX idx_attr ON products USING gin (attributes [1]);

Drag options to blanks, or click blank then click option'

Ajsonb_ops

Bjson_ops

Cjsonb_path_ops

Djson_path_ops

Attempts:

3 left

💡 Hint

Common Mistakes

Using json_ops which is for JSON, not JSONB.

Using json_path_ops which is invalid.

Using jsonb_path_ops which is valid but not the default.

The correct operator class for a general GIN index on JSONB is jsonb_ops. The others are invalid or for JSON, not JSONB.

4fill in blank

hard

Fill both blanks to create a GIN index on the JSONB column 'details' using the default operator class.

PostgreSQL

CREATE INDEX idx_details ON orders USING [1] (details [2]);

Drag options to blanks, or click blank then click option'

Agin

Bbtree

Cjsonb_ops

Dhash

Attempts:

3 left

💡 Hint

Common Mistakes

Using btree index type which is invalid for JSONB.

Using hash index type which is not supported for JSONB.

Using jsonb_path_ops instead of jsonb_ops for default.

The index type must be gin and the operator class for JSONB default is jsonb_ops.

5fill in blank

hard

Fill all three blanks to create a GIN index on the JSONB column 'config' with the jsonb_path_ops operator class and a custom index name.

PostgreSQL

CREATE INDEX [1] ON settings USING [2] (config [3]);

Drag options to blanks, or click blank then click option'

Aidx_config_gin

Bgin

Cjsonb_path_ops

Dbtree

Attempts:

3 left

💡 Hint

Common Mistakes

Using btree index type which is invalid for JSONB.

Using jsonb_ops instead of jsonb_path_ops for optimized queries.

Not providing a custom index name.

The index name can be custom like idx_config_gin. The index type is gin and the operator class for optimized JSONB path queries is jsonb_path_ops.