MCQs on Advanced Data Types and JSON | PostgreSQL Database

Dive into PostgreSQL’s advanced data types and JSON capabilities with this set of 30 MCQs. Explore arrays, JSON/JSONB, JSON query functions, and full-text search to master database management and optimization.

---

Working with Arrays and Multidimensional Data

1. Which PostgreSQL data type is used to store an array of integers?
   • A) INT[]
   • B) INTEGER[]
   • C) ARRAY(INT)
   • D) LIST(INT)
2. How do you declare an array with a fixed size in PostgreSQL?
   • A) INT[5]
   • B) ARRAY[5]
   • C) INT[5] DEFAULT {1,2,3,4,5}
   • D) INT ARRAY[5]
3. How do you access the second element of an array in PostgreSQL?
   • A) array[2]
   • B) array[1]
   • C) array[2, 1]
   • D) array(2)
4. How do you add an element to the end of an array in PostgreSQL?
   • A) array_add(array, 5)
   • B) array[5]
   • C) array || 5
   • D) array + 5
5. Which function can be used to concatenate two arrays in PostgreSQL?
   • A) array_concat
   • B) array_append
   • C) || (double pipe)
   • D) array_merge
6. What type of indexing can improve the performance of array queries in PostgreSQL?
   • A) Hash Index
   • B) GiST Index
   • C) B-tree Index
   • D) GIN Index
7. How do you check if a specific value exists in an array in PostgreSQL?
   • A) array.contains(value)
   • B) value IN array
   • C) array(value)
   • D) array_has(value)
8. How are multidimensional arrays represented in PostgreSQL?
   • A) ARRAY[ARRAY[1,2], ARRAY[3,4]]
   • B) ARRAY[1, 2, 3, 4]
   • C) ARRAY[[1, 2], [3, 4]]
   • D) ARRAY{1, 2, 3, 4}

---

Using JSON/JSONB Data Types

9. What is the primary difference between the JSON and JSONB data types in PostgreSQL?
   • A) JSONB is slower for querying
   • B) JSONB stores data as binary, offering faster performance
   • C) JSON supports indexing
   • D) There is no difference
10. Which PostgreSQL function can be used to convert a JSON string to a JSONB object?
    • A) json_to_jsonb()
    • B) jsonb_parse()
    • C) to_jsonb()
    • D) jsonb_from_json()
11. How do you extract a JSON value by key in PostgreSQL?
    • A) json->'key'
    • B) json_extract('key')
    • C) json_value('key')
    • D) json.key()
12. What is the default format for the jsonb data type in PostgreSQL?
    • A) Unsorted and uncompressed
    • B) Sorted and compressed
    • C) Unsorted but compressed
    • D) Sorted and uncompressed
13. Which function is used to query JSONB arrays in PostgreSQL?
    • A) jsonb_array_elements()
    • B) jsonb_array_fetch()
    • C) jsonb_extract_array()
    • D) jsonb_get_array()
14. How do you insert a JSON object into a JSONB column in PostgreSQL?
    • A) INSERT INTO table(jsonb_column) VALUES ('{"key":"value"}')
    • B) INSERT INTO table(json_column) VALUES ('{key: value}')
    • C) INSERT INTO table(jsonb_column) VALUES ('key:value')
    • D) INSERT INTO table(jsonb_column) VALUES ('key:value')
15. Which operator can you use to check if a JSON object contains a key in PostgreSQL?
    • A) jsonb_has_key()
    • B) @>
    • C) jsonb_contains_key()
    • D) jsonb_has()

---

JSON Query Functions (json_build_object, jsonb_set)

16. What does the json_build_object() function do in PostgreSQL?
    • A) Converts JSON data to an object
    • B) Constructs a JSON object from key-value pairs
    • C) Builds a JSON array
    • D) Converts a JSONB object to JSON
17. Which function in PostgreSQL allows updating a specific key’s value in a JSONB object?
    • A) jsonb_set()
    • B) jsonb_update()
    • C) json_set()
    • D) jsonb_modify()
18. Which of the following is a valid argument for the jsonb_set() function?
    • A) JSON object, key, new value, [create missing]
    • B) JSON array, key, new value
    • C) Key, new value
    • D) JSON object, key
19. How would you add a new key-value pair to an existing JSON object using jsonb_set()?
    • A) jsonb_set(json_object, '{key}', 'value')
    • B) jsonb_set(json_object, 'key', 'value')
    • C) jsonb_add(json_object, '{key}', 'value')
    • D) jsonb_insert(json_object, '{key}', 'value')
20. How do you remove a key from a JSON object in PostgreSQL?
    • A) jsonb_remove()
    • B) jsonb_delete()
    • C) jsonb_set() with NULL
    • D) jsonb_drop()

---

Full-Text Search

21. Which PostgreSQL extension is required for full-text search?
    • A) pg_fulltext
    • B) pg_search
    • C) pg_trgm
    • D) pg_tsvector
22. What is a tsvector in PostgreSQL?
    • A) A data type used for storing JSON
    • B) A data type used to store full-text search data
    • C) A function to perform full-text search
    • D) A column used for indexing JSONB data
23. Which of the following is used to create a full-text search index in PostgreSQL?
    • A) CREATE INDEX ON table USING gin(tsvector_column)
    • B) CREATE FULLTEXT INDEX ON table
    • C) CREATE GIN INDEX ON table USING tsvector
    • D) CREATE INDEX ON table USING btree(tsvector_column)
24. Which operator is used in PostgreSQL full-text search to match a word in a tsvector column?
    • A) @@
    • B) ==
    • C) ~=
    • D) ==~
25. Which function can you use to convert text to a tsvector type for full-text search in PostgreSQL?
    • A) to_tsvector()
    • B) text_to_search()
    • C) tsvector()
    • D) search_text()
26. Which of the following queries uses a full-text search in PostgreSQL?
    • A) SELECT * FROM table WHERE column @@ 'search_term'
    • B) SELECT * FROM table WHERE column CONTAINS 'search_term'
    • C) SELECT * FROM table WHERE column MATCHES 'search_term'
    • D) SELECT * FROM table WHERE column FULLTEXT 'search_term'
27. What does the ts_rank() function do in PostgreSQL?
    • A) Ranks search results based on relevance
    • B) Calculates the word frequency
    • C) Returns the most frequent term
    • D) Ranks the rows based on the number of words
28. How can you include multiple words in a full-text search query in PostgreSQL?
    • A) Use the AND operator between terms
    • B) Use the + operator between terms
    • C) Use the OR operator between terms
    • D) Use the IN operator between terms
29. Which function would you use to tokenize a string for full-text search in PostgreSQL?
    • A) to_tsvector()
    • B) text_tokenize()
    • C) ts_tokenize()
    • D) tokenize_text()
30. What is the purpose of the pg_trgm extension in PostgreSQL?
    • A) For full-text search using trigram similarity
    • B) For efficient indexing of JSON data
    • C) For text tokenization
    • D) For regular expression matching

---

Answers:

Qno	Answer
1	A) INT[]
2	A) INT[5]
3	A) array[2]
4	C) `array
5	C) `
6	D) GIN Index
7	B) value IN array
8	A) ARRAY[ARRAY[1,2], ARRAY[3,4]]
9	B) JSONB stores data as binary, offering faster performance
10	C) to_jsonb()
11	A) json->'key'
12	B) Sorted and compressed
13	A) jsonb_array_elements()
14	A) INSERT INTO table(jsonb_column) VALUES ('{"key":"value"}')
15	B) @>
16	B) Constructs a JSON object from key-value pairs
17	A) jsonb_set()
18	A) JSON object, key, new value, [create missing]
19	A) jsonb_set(json_object, '{key}', 'value')
20	C) jsonb_set() with NULL
21	D) pg_tsvector
22	B) A data type used to store full-text search data
23	A) CREATE INDEX ON table USING gin(tsvector_column)
24	A) @@
25	A) to_tsvector()
26	A) SELECT * FROM table WHERE column @@ 'search_term'
27	A) Ranks search results based on relevance
28	B) Use the + operator between terms
29	A) to_tsvector()
30	A) For full-text search using trigram similarity