PostgreSQL JSON SELECT Quiz: Test Your SQL Skills

Progress

True / False

In PostgreSQL, JSON data type can be used to store JSON-formatted text.

True False

PostgreSQL supports JSON data type which can store JSON (JavaScript Object Notation) formatted text.

True / False

The ->> operator in PostgreSQL JSON queries returns the JSON object.

True False

The ->> operator in PostgreSQL returns the JSON value as text. The -> operator returns the JSON object.

True / False

In PostgreSQL, you can use the jsonb data type to store JSON data.

True False

PostgreSQL provides json and jsonb data types for storing JSON data. jsonb stores data in a binary format which is more efficient for querying.

True / False

The jsonb_each_text function in PostgreSQL returns set of key-value pairs from a JSONB object.

True False

The jsonb_each_text function in PostgreSQL returns a set of key-value pairs, where keys and values are both of type text.

True / False

PostgreSQL allows indexing of JSONB data using GIN (Generalized Inverted Index).

True False

PostgreSQL supports indexing JSONB data using GIN, which is efficient for searching within JSONB documents.

True / False

The #>> operator in PostgreSQL can be used to extract a JSON object at the specified path.

True False

: The #>> operator is used to extract the JSON object as text at the specified path. The #> operator is used to extract the JSON object.

True / False

PostgreSQL's JSON functions do not support JSON path queries.

True False

PostgreSQL supports JSON path queries using the jsonpath functions and operators introduced in version 12.

True / False

The jsonb_set function in PostgreSQL can be used to update values in a JSONB document.

True False

The jsonb_set function is used to update the value of a JSONB document at a specified path.

True / False

PostgreSQL supports filtering rows based on JSONB array length using the jsonb_array_length function.

True False

The jsonb_array_length function returns the number of elements in a JSONB array, which can be used in WHERE clauses for filtering.

Q10

True / False

In PostgreSQL, the jsonb_each function can be used to transform JSONB data into a set of records with keys and values as separate columns.

True False

The jsonb_each function returns a set of key-value pairs from a JSONB object, making it easier to work with JSONB data in a relational manner.

Q11

Single Choice

Which function in PostgreSQL is used to extract a JSON value as text using a JSON path?

json_extract_text json_get_text jsonb_extract_textjson_extract_value

In PostgreSQL, jsonb_extract_text is used to extract a JSON value as text using a JSON path.

Q12

Single Choice

How can you access the value of a key named "name" from a JSON column called "data" in PostgreSQL?value as text using a JSON path.

SELECT data->>'name' FROM table_name; SELECT data->'name' FROM table_name; SELECT data->>name FROM table_name;SELECT data->name FROM table_name;

The ->> operator in PostgreSQL is used to access the value of a key as text from a JSON column.

Q13

Single Choice

What is the difference between -> and ->> operators in PostgreSQL JSON functions?

-> returns text, ->> returns JSON -> returns JSON, ->> returns text Both return the same value-> and ->> are used interchangeably

In PostgreSQL, -> is used to extract a JSON object or array, while ->> is used to extract the text value.

Q14

Single Choice

Which of the following SQL statements would you use to retrieve a nested JSON key in PostgreSQL?

SQL Code

SELECT data->'address'->>'city' FROM users;

The query is invalid in PostgreSQL The query retrieves the "city" value from the nested "address" JSON key The query retrieves the entire "address" object as JSONThe query returns NULL

The SQL query uses PostgreSQL's JSON operators to drill down into nested JSON structures and extract the "city" value.

Q15

Single Choice

Given the following JSON column structure, how would you retrieve all rows where the "active" key is true?

SQL Code

{"id": 1, "active": true}

SELECT * FROM table_name WHERE data->>'active' = 'true'; SELECT * FROM table_name WHERE data->'active' = 'true'; SELECT * FROM table_name WHERE data->>'active' = true;SELECT * FROM table_name WHERE data->'active' = true;

The ->> operator is used to extract the value as text, so you need to compare it against the string 'true' in PostgreSQL.

Q16

Single Choice

How can you convert a JSON array into a PostgreSQL row set using the jsonb_array_elements function?

SQL Code

SELECT jsonb_array_elements(data->'items') FROM table_name;

This query flattens the JSON array into a set of JSON objects This query converts each JSON object into a separate column This query returns the entire JSON array as a single columnThis query returns NULL if the array is empty

The jsonb_array_elements function in PostgreSQL converts each element of a JSON array into a separate row, effectively flattening the array.

Q17

Single Choice

How do you use PostgreSQL's JSON functions to extract all "names" from a JSON column that contains an array of objects?

SQL Code

SELECT jsonb_array_elements(data->'items')->>'name' FROM table_name;

This query returns the first "name" in the array This query returns all "name" fields from the array This query returns NULL if "name" does not existThis query throws an error if "name" is not a text field

The query uses jsonb_array_elements to expand the JSON array into rows and then uses ->> to extract the "name" field from each object.

Q18

Single Choice

How can you update a specific key within a JSON column in PostgreSQL using the jsonb_set function?

SQL Code

UPDATE table_name SET data = jsonb_set(data, '{key}', '"new_value"') WHERE id = 1;

This query updates the entire JSON object This query updates only the specified key within the JSON object This query creates a new key if it does not existThis query fails if the key is not found

The jsonb_set function in PostgreSQL is used to update a specific key within a JSON object, creating a new key if necessary.

Q19

Single Choice

Which function would you use to test if a specific key exists within a JSON object in PostgreSQL?

SQL Code

SELECT data ? 'key' FROM table_name;

jsonb_exists jsonb_has_key ?jsonb_key_exists

The ? operator is used in PostgreSQL to test if a specific key exists within a JSON object.

Q20

Single Choice

In PostgreSQL, which function would you use to aggregate JSON data into a JSON array?

SQL Code

SELECT json_agg(data) FROM table_name;

jsonb_agg jsonb_array_agg json_aggjson_array_agg

The json_agg function in PostgreSQL aggregates JSON data into a JSON array, allowing you to collect all rows into a single JSON array.

Q21

Multiple Choice

Which of the following queries correctly selects a specific key from a JSON column?

SELECT data->>'product_name' FROM products; SELECT data->'product_name' FROM products; SELECT data->>'product_id' FROM products;SELECT json_extract_path_text(data, 'product_name') FROM products;

The '->>' operator is used to extract the value associated with the key in text format, while '->' returns the JSON object.

Q22

Multiple Choice

Which SQL queries correctly extract nested JSON data?

SELECT details->'customer'->>'name' FROM orders; SELECT details->>'customer'->'name' FROM orders; SELECT details->'customer'->'name' FROM orders;SELECT details->'customer'->>'name' FROM orders WHERE details->>'status' = 'completed';

The '->' operator is used for JSON object extraction, and chaining these operators allows you to access nested objects.

Q23

Multiple Choice

Which SQL queries correctly filter rows based on a JSON key value?

SELECT * FROM transactions WHERE metadata->>'status' = 'approved'; SELECT * FROM transactions WHERE metadata->'status' = 'approved'; SELECT * FROM transactions WHERE json_extract_path_text(metadata, 'status') = 'approved';SELECT * FROM transactions WHERE metadata->>'approval'->>'status' = 'approved';

The '->>' operator can be used in a WHERE clause to filter rows based on the value of a JSON key.

Q24

Multiple Choice

Which SQL queries correctly select all keys from a JSON object in a column?

SELECT jsonb_object_keys(settings) FROM users; SELECT json_object_keys(settings) FROM users; SELECT settings->>'all_keys' FROM users;SELECT jsonb_object_keys(settings) FROM users WHERE settings->>'active' = 'true';

The 'jsonb_object_keys()' function is used to return the set of keys in a JSONB object.

Q25

Multiple Choice

Which SQL queries correctly select multiple values from a JSON object?

SELECT order_details->>'product_id', order_details->>'quantity' FROM orders; SELECT order_details->'product_id', order_details->'quantity' FROM orders; SELECT json_extract_path_text(order_details, 'product_id'), json_extract_path_text(order_details, 'quantity') FROM orders;SELECT order_details->>'product_id', order_details->>'quantity' FROM orders WHERE order_details->>'status' = 'shipped';

Multiple keys can be selected by chaining the '->>' operators or using multiple columns in the SELECT statement.

Q26

Multiple Choice

Which SQL queries correctly check if a JSON key exists in a column?

SELECT * FROM products WHERE attributes ? 'discount'; SELECT * FROM products WHERE attributes->>'discount' IS NOT NULL; SELECT * FROM products WHERE jsonb_exists(attributes, 'discount');SELECT * FROM products WHERE jsonb_exists_path(attributes, '{discount}');

The '?'' operator is used to check if a JSON key exists, and the 'jsonb_exists()' function can also be used.

Q27

Multiple Choice

Which SQL queries correctly aggregate data based on a JSON key value?

SELECT COUNT(*) FROM orders WHERE details->>'status' = 'completed'; SELECT COUNT(*) FROM orders WHERE json_extract_path_text(details, 'status') = 'completed'; SELECT COUNT(*) FROM orders WHERE details->'status' = 'completed';SELECT COUNT(*) FROM orders WHERE details ? 'status' AND details->>'status' = 'completed';

JSON keys can be used in aggregation functions like COUNT by extracting the key's value in the WHERE clause.

Q28

Multiple Choice

Which SQL queries correctly select JSON data with conditions on nested objects?

SELECT * FROM orders WHERE order_data->'shipping'->>'country' = 'USA'; SELECT * FROM orders WHERE order_data->'shipping'->>'country' = 'USA' AND order_data->>'status' = 'shipped'; SELECT * FROM orders WHERE json_extract_path_text(order_data, 'shipping', 'country') = 'USA';SELECT * FROM orders WHERE order_data->>'country' = 'USA';

To filter based on nested JSON objects, chaining of '->' and '->>' operators is required.

Q29

Multiple Choice

Which SQL queries correctly update a value in a JSON object?

UPDATE products SET product_data = jsonb_set(product_data, '{price}', '100') WHERE product_data->>'category' = 'Electronics'; UPDATE products SET product_data = jsonb_set(product_data, '{category}', 'Electronics') WHERE product_data->>'price' = '100'; UPDATE products SET product_data = jsonb_set(product_data, '{price}', '99.99') WHERE product_data->>'category' = 'Electronics';UPDATE products SET product_data = product_data || jsonb_build_object('price', '99.99') WHERE product_data->>'category' = 'Electronics';

To update a JSON value, the JSONB_SET function can be used, which allows you to modify JSON data in a column.

Q30

Multiple Choice

Which SQL queries correctly delete a key-value pair from a JSON object?

UPDATE orders SET details = details - 'discount'; UPDATE orders SET details = details - '{discount}'; UPDATE orders SET details = jsonb_delete(details, 'discount');UPDATE orders SET details = jsonb_set(details, '{discount}', NULL);

The '-'' operator can be used to remove a key from a JSONB object, and the JSONB_DELETE function is an alternative method.

PostgreSQL Database Quiz Questions