Test Your PostgreSQL JSON Data Type Skills with This Quiz

Progress

True / False

PostgreSQL allows storing JSON data directly in its tables.

True False

PostgreSQL supports the JSON data type, allowing JSON data to be stored and queried directly in tables

True / False

The JSON data type in PostgreSQL automatically validates JSON syntax on insertion.

True False

When inserting data into a JSON column, PostgreSQL ensures the data is valid JSON format.

True / False

PostgreSQL can only store JSON data as text strings.

True False

PostgreSQL has specific JSON and JSONB data types designed to store JSON data efficiently.

True / False

PostgreSQL’s JSONB type is more efficient for indexing and querying compared to the JSON type.

True False

JSONB stores JSON data in a binary format, which is more efficient for indexing and querying.

True / False

You can use the ->> operator to extract an element from a JSON array in PostgreSQL.

True False

The ->> operator is used to extract a JSON object field as text, not to extract an element from a JSON array. For JSON arrays, use the -> operator.

True / False

PostgreSQL provides functions to transform JSON data into PostgreSQL rows and vice versa.

True False

PostgreSQL offers functions like json_to_record and json_to_recordset to convert JSON data to rows, and row_to_json to transform rows into JSON.

True / False

In PostgreSQL, indexing a JSONB column with a GIN index allows for efficient search of nested keys and values.

True False

GIN indexes are particularly effective for JSONB columns, providing fast access to nested keys and values.

True / False

PostgreSQL’s JSONB type preserves the original formatting and ordering of JSON data.

True False

JSONB does not preserve the original formatting or ordering of JSON data; it stores data in a binary format optimized for search.

True / False

You can use the jsonb_set function in PostgreSQL to update a value in a JSONB column.

True False

The jsonb_set function allows you to update a value within a JSONB column without replacing the entire JSON document.

Q10

True / False

PostgreSQL's JSONB type is always preferred over JSON for all types of operations because it is universally more efficient.

True False

While JSONB is generally more efficient for indexing and querying, JSON might be preferred in scenarios where maintaining the original text formatting and ordering of JSON data is critical, or for certain operations where the overhead of converting to binary is not justified.

Q11

Single Choice

Which MySQL data type should you use to store JSON data?

TEXT BLOB JSONVARCHAR

MySQL provides a native JSON data type to store JSON-formatted data. This allows MySQL to optimize the storage and querying of JSON documents.

Q12

Single Choice

What will happen if you try to insert invalid JSON data into a MySQL JSON column?

MySQL will automatically correct the data The insertion will succeed without any error MySQL will throw an error and reject the dataMySQL will convert the data to a string

MySQL strictly enforces the JSON format.

Q13

Single Choice

Which MySQL function is used to validate whether a string is in valid JSON format?

JSON_EXTRACT() IS_JSON() JSON_VALID()JSON_TYPE()

The JSON_VALID() function checks if a given string is a valid JSON document. It returns 1 if the string is valid JSON, otherwise 0.

Q14

Single Choice

What will be the result of the following query?

SQL Code

SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');

John {"name": "John"} "John"name

The JSON_EXTRACT() function extracts data from a JSON document. The $ symbol represents the root, and $.name extracts the value associated with the "name" key, returning it as a JSON string.

Q15

Single Choice

How can you modify the value of a key in a JSON column in MySQL?

SQL Code

UPDATE employees SET details = JSON_SET(details, '$.salary', 50000) WHERE id = 1;

JSON_CHANGE JSON_MODIFY JSON_SETJSON_UPDATE

The JSON_SET() function updates or inserts values into a JSON document. It modifies the value associated with a key in a JSON column without changing the rest of the document.

Q16

Single Choice

What does the following query return?

SQL Code

SELECT JSON_UNQUOTE(JSON_EXTRACT('{"city": "New York"}', '$.city'));

New York "New York" City{"city": "New York"}

JSON_UNQUOTE() removes the double quotes from the JSON string returned by JSON_EXTRACT(). Hence, the query returns the unquoted string New York.

Q17

Single Choice

Which MySQL function would you use to merge two JSON documents into a single JSON document?

SQL Code

SELECT JSON_MERGE_PRESERVE('{"a": 1}', '{"b": 2}');

JSON_MERGE() JSON_MERGE_PATCH() JSON_UNION()JSON_MERGE_PRESERVE()

JSON_MERGE_PRESERVE() merges two JSON documents while preserving duplicate keys by creating an array of the values. The function is deprecated in favor of JSON_MERGE_PATCH() in newer MySQL versions.

Q18

Single Choice

What will be the result of the following query?

SQL Code

SELECT JSON_ARRAYAGG(name) FROM employees;

["John", "Doe", "Jane"] "John", "Doe", "Jane" {"names": ["John", "Doe", "Jane"]}[{"name": "John"}, {"name": "Doe"}, {"name": "Jane"}]

JSON_ARRAYAGG() aggregates the values from the name column into a single JSON array, returning an array of names.

Q19

Single Choice

What will be the output of the following MySQL query?

SQL Code

SELECT JSON_DEPTH('{"name": "John", "address": {"city": "New York", "zip": "10001"}}');

2 3 45

The JSON_DEPTH() function returns the maximum depth of a JSON document. In this case, the depth of the provided JSON document is 3.

Q20

Single Choice

In MySQL, which of the following operations will raise an error?

SQL Code

INSERT INTO orders (details) VALUES ('{"order_id": 123, "amount": 250}');

Inserting a JSON string without using the JSON data type Inserting a JSON string without using JSON functions Inserting a non-JSON string into a JSON columnInserting a JSON string into a TEXT column

MySQL raises an error if you try to insert a non-JSON string into a column declared as the JSON data type. The inserted value must be a valid JSON document; otherwise, MySQL will reject the operation.

Q21

Multiple Choice

Which SQL command defines a column with a JSON data type in PostgreSQL?

SQL Code

CREATE TABLE users (
 user_id SERIAL PRIMARY KEY,
 user_data JSON NOT NULL
);

CREATE TABLE users ( user_id SERIAL PRIMARY KEY, user_data JSON NOT NULL ); CREATE TABLE users ( user_id SERIAL PRIMARY KEY, user_data JSONB NOT NULL ); CREATE TABLE users ( user_id SERIAL PRIMARY KEY, user_data TEXT NOT NULL );CREATE TABLE users ( user_id SERIAL PRIMARY KEY, user_data VARCHAR NOT NULL );

This command creates a 'users' table where the 'user_data' column is defined as JSON, allowing storage of JSON-encoded data.

Q22

Multiple Choice

Which SQL command defines a column with a JSONB data type in PostgreSQL?

SQL Code

CREATE TABLE logs (
 log_id SERIAL PRIMARY KEY,
 log_data JSONB NOT NULL
);

CREATE TABLE logs ( log_id SERIAL PRIMARY KEY, log_data JSONB NOT NULL ); CREATE TABLE logs ( log_id SERIAL PRIMARY KEY, log_data JSON NOT NULL ); CREATE TABLE logs ( log_id SERIAL PRIMARY KEY, log_data TEXT NOT NULL );CREATE TABLE logs ( log_id SERIAL PRIMARY KEY, log_data BYTEA NOT NULL );

This command creates a 'logs' table where the 'log_data' column is defined as JSONB, allowing storage of JSON data in a binary format for efficient access.

Q23

Multiple Choice

Which SQL command creates a table with a JSON column that has a default value in PostgreSQL?

SQL Code

CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 order_details JSON NOT NULL DEFAULT '{}'
);

CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, order_details JSON NOT NULL DEFAULT '{}' ); CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, order_details JSON NOT NULL DEFAULT '[]' ); CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, order_details JSONB NOT NULL DEFAULT '{}' );CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, order_details JSONB NOT NULL DEFAULT '[]' );

This command creates an 'orders' table where the 'order_details' column is defined as JSON and has a default value of an empty JSON object.

Q24

Multiple Choice

Which SQL command creates a table with a JSON column and a constraint that ensures valid JSON data in PostgreSQL?

SQL Code

CREATE TABLE settings (
 setting_id SERIAL PRIMARY KEY,
 config JSON NOT NULL,
 CONSTRAINT valid_json CHECK (jsonb_typeof(config) IS NOT NULL)
);

CREATE TABLE settings ( setting_id SERIAL PRIMARY KEY, config JSON NOT NULL, CONSTRAINT valid_json CHECK (jsonb_typeof(config) IS NOT NULL) ); CREATE TABLE settings ( setting_id SERIAL PRIMARY KEY, config JSONB NOT NULL, CONSTRAINT valid_json CHECK (jsonb_typeof(config) IS NOT NULL) ); CREATE TABLE settings ( setting_id SERIAL PRIMARY KEY, config JSON NOT NULL );CREATE TABLE settings ( setting_id SERIAL PRIMARY KEY, config JSONB NOT NULL );

This command creates a 'settings' table with a JSON column 'config' and adds a CHECK constraint to ensure that only valid JSON data is stored.

Q25

Multiple Choice

Which SQL command creates a table with a JSONB column and indexes a specific field within the JSONB data in PostgreSQL?

SQL Code

CREATE TABLE products (
 product_id SERIAL PRIMARY KEY,
 product_info JSONB NOT NULL
);
CREATE INDEX idx_product_info ON products USING gin ((product_info->>'name'));

CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product_info JSONB NOT NULL ); CREATE INDEX idx_product_info ON products USING gin ((product_info->>'name')); CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product_info JSON NOT NULL ); CREATE INDEX idx_product_info ON products USING btree ((product_info->>'name')); CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product_info JSONB NOT NULL ); CREATE INDEX idx_product_info ON products USING gin ((product_info->>'category'));CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product_info JSONB NOT NULL ); CREATE INDEX idx_product_info ON products ((product_info->>'name'));

This command creates a 'products' table with a JSONB column 'product_info' and creates a GIN index on the 'name' field within the JSONB data, enhancing query performance for that field.

Q26

Multiple Choice

Which SQL command alters an existing table to add a JSON column in PostgreSQL?

SQL Code

ALTER TABLE users
ADD COLUMN preferences JSON DEFAULT '{}';

ALTER TABLE users ADD COLUMN preferences JSON DEFAULT '{}'; ALTER TABLE users ADD COLUMN preferences JSONB DEFAULT '{}'; ALTER TABLE users ADD preferences JSON DEFAULT '[]';ALTER TABLE users ADD COLUMN preferences JSON DEFAULT '[]';

This command adds a new column 'preferences' to the 'users' table, defining it as JSON and setting a default value of an empty JSON object.

Q27

Multiple Choice

Which SQL command creates a table with a JSON column that includes nested JSON data in PostgreSQL?

SQL Code

CREATE TABLE reports (
 report_id SERIAL PRIMARY KEY,
 report_data JSONB NOT NULL
);

INSERT INTO reports (report_data) VALUES ('{"meta": {"author": "John", "created": "2024-01-01"}}');

CREATE TABLE reports ( report_id SERIAL PRIMARY KEY, report_data JSONB NOT NULL ); INSERT INTO reports (report_data) VALUES ('{"meta": {"author": "John", "created": "2024-01-01"}}'); CREATE TABLE reports ( report_id SERIAL PRIMARY KEY, report_data JSON NOT NULL ); INSERT INTO reports (report_data) VALUES ('{"meta": {"author": "John", "created": "2024-01-01"}}'); CREATE TABLE reports ( report_id SERIAL PRIMARY KEY, report_data TEXT NOT NULL ); INSERT INTO reports (report_data) VALUES ('{"meta": {"author": "John", "created": "2024-01-01"}}');CREATE TABLE reports ( report_id SERIAL PRIMARY KEY, report_data JSONB NOT NULL ); INSERT INTO reports (report_data) VALUES ('{"meta": {"author": "John", "created": "2024-01-01"}}');

This command creates a 'reports' table with a JSONB column 'report_data' and inserts nested JSON data into this column, demonstrating how JSON data can be structured.

Q28

Multiple Choice

Which SQL command creates a table with a JSONB column and a partial index in PostgreSQL?

SQL Code

CREATE TABLE sessions (
 session_id SERIAL PRIMARY KEY,
 session_data JSONB NOT NULL
);

CREATE INDEX idx_active_sessions ON sessions USING gin (session_data) WHERE (session_data->>'active') = 'true';

CREATE TABLE sessions ( session_id SERIAL PRIMARY KEY, session_data JSONB NOT NULL ); CREATE INDEX idx_active_sessions ON sessions USING gin (session_data) WHERE (session_data->>'active') = 'true'; CREATE TABLE sessions ( session_id SERIAL PRIMARY KEY, session_data JSONB NOT NULL ); CREATE INDEX idx_active_sessions ON sessions (session_data->>'active'); CREATE TABLE sessions ( session_id SERIAL PRIMARY KEY, session_data JSON NOT NULL ); CREATE INDEX idx_active_sessions ON sessions USING gin (session_data->>'active');CREATE TABLE sessions ( session_id SERIAL PRIMARY KEY, session_data JSONB NOT NULL ); CREATE INDEX idx_active_sessions ON sessions USING btree (session_data) WHERE (session_data->>'active') = 'true';

This command creates a 'sessions' table with a JSONB column 'session_data' and a partial GIN index on the 'active' field within the JSONB data, optimizing queries for active sessions.

Q29

Multiple Choice

Which SQL command creates a table with a JSONB column and uses a function to extract data from the JSONB column in PostgreSQL?

SQL Code

CREATE TABLE config (
 config_id SERIAL PRIMARY KEY,
 config_data JSONB NOT NULL
);

SELECT config_data->>'setting' AS setting FROM config WHERE config_data->>'setting' = 'enabled';

CREATE TABLE config ( config_id SERIAL PRIMARY KEY, config_data JSONB NOT NULL ); SELECT config_data->>'setting' AS setting FROM config WHERE config_data->>'setting' = 'enabled'; CREATE TABLE config ( config_id SERIAL PRIMARY KEY, config_data JSON NOT NULL ); SELECT config_data->>'setting' AS setting FROM config WHERE config_data->>'setting' = 'enabled'; CREATE TABLE config ( config_id SERIAL PRIMARY KEY, config_data JSONB NOT NULL ); SELECT config_data->>'setting' AS setting FROM config WHERE config_data->>'status' = 'enabled';CREATE TABLE config ( config_id SERIAL PRIMARY KEY, config_data JSONB NOT NULL ); SELECT config_data->'setting' AS setting FROM config WHERE config_data->>'setting' = 'enabled';

This command creates a 'config' table with a JSONB column 'config_data' and demonstrates how to extract and query specific data from the JSONB column using the '->>' operator.

Q30

Multiple Choice

Which SQL command creates a table with a JSON column and checks for the existence of a key within the JSON data in PostgreSQL?

SQL Code

CREATE TABLE settings (
 setting_id SERIAL PRIMARY KEY,
 setting_data JSONB NOT NULL
);

SELECT * FROM settings WHERE setting_data ? 'theme';

CREATE TABLE settings ( setting_id SERIAL PRIMARY KEY, setting_data JSONB NOT NULL ); SELECT * FROM settings WHERE setting_data ? 'theme'; CREATE TABLE settings ( setting_id SERIAL PRIMARY KEY, setting_data JSONB NOT NULL ); SELECT * FROM settings WHERE setting_data->>'theme' = 'dark'; CREATE TABLE settings ( setting_id SERIAL PRIMARY KEY, setting_data JSONB NOT NULL ); SELECT * FROM settings WHERE setting_data ?& ARRAY['theme'];CREATE TABLE settings ( setting_id SERIAL PRIMARY KEY, setting_data JSONB NOT NULL ); SELECT * FROM settings WHERE setting_data->'theme' = 'dark';

This command creates a 'settings' table with a JSONB column 'setting_data' and queries the table to check if the 'theme' key exists within the JSON data using the '?' operator.

PostgreSQL Database Quiz Questions