Why JSON support matters in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why use JSON data type in PostgreSQL?

Which of the following is the main advantage of using the JSON data type in PostgreSQL compared to storing JSON as plain text?

A It allows PostgreSQL to validate and query JSON data efficiently using built-in functions and indexes.

B It automatically compresses JSON data to save disk space without any extra configuration.

C It converts JSON data into XML format for better compatibility with other systems.

D It prevents any changes to the JSON data once inserted, making it immutable.

💡 Hint

Think about how PostgreSQL can work with JSON data beyond just storing it as text.

PostgreSQL's JSON data type allows the database to parse, validate, and index JSON data. This makes querying parts of the JSON efficient and reliable, unlike plain text which is just stored as a string.

❓ query_result

intermediate

Querying JSON data in PostgreSQL

Given a table 'users' with a JSON column 'profile' storing {"age": 30, "city": "Boston"}, what is the output of this query? SELECT profile->>'city' FROM users WHERE profile->>'age' = '30';

PostgreSQL

CREATE TABLE users (id SERIAL PRIMARY KEY, profile JSON);
INSERT INTO users (profile) VALUES ('{"age": 30, "city": "Boston"}');
INSERT INTO users (profile) VALUES ('{"age": 25, "city": "Seattle"}');

A Boston

B 30

C Seattle

D NULL

💡 Hint

The ->> operator extracts the JSON value as text for the given key.

The query filters users where the 'age' key in the JSON equals '30' (as text). It then selects the 'city' value from the JSON, which is 'Boston' for that user.

📝 Syntax

advanced

Correct JSON query syntax in PostgreSQL

Which of the following queries correctly extracts the value of the key 'status' from a JSON column 'data' in table 'orders'?

A SELECT data->'status' FROM orders;

B SELECT data->>status FROM orders;

C SELECT data['status'] FROM orders;

D SELECT data->>'status' FROM orders;

💡 Hint

Use the operator that returns the JSON value as text for easy reading.

The ->> operator extracts the JSON value as text, which is usually what you want when reading JSON fields. Option D returns JSON type, B is invalid syntax, and D misses quotes around the key.

❓ optimization

advanced

Improving JSON query performance in PostgreSQL

Which method improves query performance when filtering rows based on a JSON key's value in PostgreSQL?

A Use a trigger to copy JSON data into a separate text column and index it.

B Store JSON data as plain text and use LIKE queries.

C Create a GIN index on the JSON column.

D Convert JSON to XML before storing for faster queries.

💡 Hint

PostgreSQL supports special indexes for JSON data to speed up queries.

GIN indexes are designed to index JSON data efficiently, allowing fast lookups on keys and values inside JSON columns. Other options are less efficient or add complexity.

🔧 Debug

expert

Diagnosing JSON query error in PostgreSQL

A developer runs this query: SELECT data->>'price' FROM products WHERE data->>'price' > 100; But it returns an error. What is the cause?

A The ->> operator is invalid for JSON columns; should use -> instead.

B Comparing JSON text values with > operator causes a type error; need to cast to numeric first.

C The JSON column 'data' must be cast to text before querying.

D PostgreSQL does not support comparison operators on JSON data.

💡 Hint

Think about the data type returned by ->> and how comparison operators work.

The ->> operator returns text, so comparing it directly with a number using > causes an error. Casting the text to numeric fixes this.