JSON vs JSONB differences in PostgreSQL - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Difference in Storage Format

Which statement correctly describes the difference in storage format between JSON and JSONB in PostgreSQL?

A Both JSON and JSONB store data as plain text but differ in indexing.

B JSON stores data in binary format, while JSONB stores data as plain text.

C JSON stores data as plain text, while JSONB stores data in a binary format.

D Both JSON and JSONB store data in binary format but differ in compression.

💡 Hint

Think about how the data is saved internally and how that affects performance.

JSON stores data as plain text, which means it keeps the original formatting. JSONB stores data in a binary format, which allows faster processing and indexing.

❓ query_result

intermediate

Query Result Difference on Duplicate Keys

Given a table with a JSON and a JSONB column both containing the value '{"a":1, "a":2}', what will be the result of selecting these columns?

PostgreSQL

SELECT json_col, jsonb_col FROM test_table;

A json_col returns '{"a":1, "a":2}', jsonb_col returns '{"a":2}'

B json_col returns '{"a":2}', jsonb_col returns '{"a":1, "a":2}'

C Both json_col and jsonb_col return '{"a":1, "a":2}'

D Both json_col and jsonb_col return '{"a":2}'

💡 Hint

Consider how JSONB handles duplicate keys compared to JSON.

JSON preserves duplicate keys as text, so it returns both. JSONB removes duplicates and keeps the last key-value pair.

❓ optimization

advanced

Indexing Performance

Which indexing method is supported only by JSONB and not by JSON in PostgreSQL?

A GIN (Generalized Inverted Index)

B B-tree index

C Hash index

D Bitmap index

💡 Hint

Think about which index type helps with fast key/value lookups inside JSON data.

JSONB supports GIN indexes which allow efficient searching inside the JSON structure. JSON does not support GIN indexing.

🔧 Debug

advanced

Error When Using JSON Functions

Which option will cause an error when trying to use the JSONB containment operator '@>' on a JSON column?

PostgreSQL

SELECT * FROM test_table WHERE json_col @> '{"key": "value"}';

A The query runs successfully and returns matching rows.

B The query returns no rows but does not raise an error.

C The query raises a syntax error due to wrong operator usage.

D The query raises an error: operator does not exist: json @> json.

💡 Hint

Check if the operator '@>' is supported for the data type used.

The '@>' operator is supported for JSONB but not for JSON type, so using it on a JSON column causes an operator does not exist error.

🧠 Conceptual

expert

When to Prefer JSON over JSONB

In which scenario is it better to use JSON instead of JSONB in PostgreSQL?

A When you want to avoid storing duplicate keys by default.

B When you need to preserve the exact input text including whitespace and key order.

C When you want faster query performance and indexing.

D When you want to use GIN indexes for complex queries.

💡 Hint

Think about what JSON preserves that JSONB does not.

JSON preserves the exact input text including whitespace and key order, which JSONB does not. This is useful when the original formatting matters.