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.

Practice

1. Why is JSON support important in PostgreSQL?

easy

A. It makes PostgreSQL faster than all other databases.

B. It allows storing flexible and varying data structures in one column.

C. It replaces the need for any relational tables.

D. It automatically converts JSON to XML format.

Solution

1. Step 1: Understand JSON flexibility

   JSON allows storing data that can change structure without altering the table schema.

2. Step 2: Compare with relational tables

   Relational tables require fixed columns, but JSON columns can hold different keys and values per row.

3. Final Answer:

   It allows storing flexible and varying data structures in one column. -> Option B

4. Quick Check:

   JSON = flexible data storage [OK]

Hint: JSON stores flexible data in one column easily [OK]

Common Mistakes:

• Thinking JSON makes PostgreSQL faster than all others
• Believing JSON replaces relational tables completely
• Confusing JSON with XML conversion

2. Which of the following is the correct way to declare a JSON column in a PostgreSQL table?

easy

A. CREATE TABLE data (info XML);

B. CREATE TABLE data (info TEXT[]);

C. CREATE TABLE data (info VARCHAR);

D. CREATE TABLE data (info JSON);

Solution

1. Step 1: Identify JSON data type syntax

   PostgreSQL uses the JSON or JSONB data type to store JSON data.

2. Step 2: Check each option

   Only CREATE TABLE data (info JSON); uses JSON type correctly; others use unrelated types.

3. Final Answer:

   CREATE TABLE data (info JSON); -> Option D

4. Quick Check:

   JSON column = JSON type [OK]

Hint: Use JSON or JSONB type for JSON columns [OK]

Common Mistakes:

• Using TEXT[] or VARCHAR for JSON data
• Confusing JSON with XML data type
• Missing JSON keyword in column definition

3. Given the table users with a JSON column profile, what does this query return?

SELECT profile->>'name' FROM users WHERE profile->>'age' = '30';

medium

A. An error because ->> cannot be used in WHERE.

B. All JSON profiles where age is 30.

C. Names of users whose age is 30 as text values.

D. Age values of users named '30'.

Solution

1. Step 1: Understand JSON operators

   The operator ->> extracts JSON object field as text. Here, profile->>'age' gets age as text.

2. Step 2: Analyze query logic

   The WHERE clause filters rows where age equals '30'. The SELECT returns the name field as text for those rows.

3. Final Answer:

   Names of users whose age is 30 as text values. -> Option C

4. Quick Check:

   ->> extracts text field = names for age 30 [OK]

Hint: ->> extracts text; WHERE filters JSON text fields [OK]

Common Mistakes:

• Thinking ->> cannot be used in WHERE clause
• Expecting full JSON objects instead of text
• Confusing age and name fields

4. What is wrong with this query that tries to filter JSON data?

SELECT * FROM orders WHERE details->'price' = 100;

medium

A. Using -> returns JSON, so comparison with integer 100 fails.

B. The table name 'orders' is invalid in PostgreSQL.

C. The operator -> cannot be used in WHERE clause.

D. JSON data cannot be filtered in PostgreSQL.

Solution

1. Step 1: Understand JSON operators and types

   The operator -> returns JSON type, not a plain value, so comparing it directly to integer 100 causes a type mismatch.

2. Step 2: Correct usage for comparison

   Use ->> to extract text and then cast to integer for numeric comparison.

3. Final Answer:

   Using -> returns JSON, so comparison with integer 100 fails. -> Option A

4. Quick Check:

   -> returns JSON, not scalar, so comparison fails [OK]

Hint: Use ->> to get text before comparing JSON values [OK]

Common Mistakes:

• Comparing JSON object directly to integer
• Assuming -> works like ->>
• Believing JSON cannot be filtered in WHERE

5. You have a table events with a JSONB column data storing event details. You want to find all events where the nested JSON key location.city is 'Paris'. Which query correctly achieves this?

hard

A. SELECT * FROM events WHERE data->'location'->>'city' = 'Paris';

B. SELECT * FROM events WHERE data->>'location.city' = 'Paris';

C. SELECT * FROM events WHERE data->'location.city' = 'Paris';

D. SELECT * FROM events WHERE data->'location'->'city' = 'Paris';

Solution

1. Step 1: Access nested JSON keys correctly

   To get nested keys, chain -> for JSON objects and ->> for text extraction. Here, data->'location' gets the location object, then ->>'city' extracts city as text.

2. Step 2: Check each option

   data->>'location.city' treats 'location.city' as a single key. data->'location.city' does the same with ->. data->'location'->'city' returns JSON for 'city', causing type mismatch when compared to 'Paris'. Only data->'location'->>'city' extracts text correctly.

3. Final Answer:

   SELECT * FROM events WHERE data->'location'->>'city' = 'Paris'; -> Option A

4. Quick Check:

   Nested keys need chained -> and ->> [OK]

Hint: Chain -> for objects, ->> for text extraction in JSON [OK]

Common Mistakes:

• Using dot notation inside ->> operator
• Comparing JSON object directly to string
• Not chaining operators for nested keys