Creating JSON columns in PostgreSQL - Quick Revision & Summary

Recall & Review

beginner

What is a JSON column in PostgreSQL?

A JSON column stores JSON (JavaScript Object Notation) data directly in a table, allowing you to save structured data like objects or arrays inside a single column.

beginner

How do you create a JSON column in a PostgreSQL table?

Use the CREATE TABLE statement with a column type json or jsonb. For example: <br>CREATE TABLE example (data jsonb);

intermediate

What is the difference between json and jsonb types in PostgreSQL?

json stores JSON data as text, preserving formatting. jsonb stores JSON in a binary format, which is faster for searching and indexing.

beginner

Can you insert JSON data directly into a JSON column?

Yes, you can insert JSON data as a string. For example: <br>INSERT INTO example (data) VALUES ('{"name": "Alice", "age": 30}');

intermediate

Why use JSON columns instead of multiple regular columns?

JSON columns let you store flexible, nested data without changing the table structure. This is useful when data varies or has many optional fields.

Which PostgreSQL data type is best for storing JSON data with efficient querying?

A jsonb

B text

C varchar

D jsontext

The jsonb type stores JSON in a binary format optimized for querying and indexing.

How do you define a JSON column named 'info' in a new table?

A CREATE TABLE t (info jsonb);

B CREATE TABLE t (info string);

C CREATE TABLE t (info jsontext);

D CREATE TABLE t (info varchar);

Use jsonb or json as the column type to store JSON data.

Which statement is true about the json type?

A It automatically indexes JSON keys.

B It stores JSON in binary format.

C It cannot store arrays.

D It stores JSON as plain text preserving formatting.

json stores JSON as text, keeping the original formatting.

What is a benefit of using JSON columns in a database?

A Faster than all relational columns for all queries.

B Store flexible, nested data without changing table schema.

C Automatically encrypts data.

D Prevents any data duplication.

JSON columns allow flexible data storage without altering table structure.

How do you insert JSON data into a JSON column?

A INSERT INTO table (col) VALUES (jsonb_parse);

B INSERT INTO table (col) VALUES (key: value);

C INSERT INTO table (col) VALUES ('{"key": "value"}');

D INSERT INTO table (col) VALUES (json_object);

JSON data is inserted as a string with proper JSON syntax.

Explain how to create a JSON column in PostgreSQL and why you might choose jsonb over json.

Think about storage format and query performance differences.

Describe a real-life scenario where using a JSON column in a database table is helpful.

Imagine storing user profiles with optional fields.