Interview Q&A / PostgreSQL

1. What is the difference between JSON and JSONB in PostgreSQL?

   JSON stores the exact text you inserted, including spaces, key order and duplicate keys, and re-parses it on every read. JSONB stores a parsed binary form, which is slightly slower to write but much faster to query and can be indexed with GIN. Use JSONB unless you must keep the original text exactly.

   BeginnerIn-depth3 min read
2. What is the difference between -> and ->> in PostgreSQL JSONB queries?

   -> returns the value as jsonb (so you can keep navigating), while ->> returns it as text. Use ->> when comparing to strings or showing a value, and cast it for numbers.

   BeginnerQuick answer1 min read
3. How do you index one specific key inside a JSONB column?

   Create a B-tree expression index on the extracted value, such as (data ->> 'email'), and query with the same expression. This is smaller and faster than a GIN index for equality or range on one known key.

   IntermediateQuick answer1 min read
4. How do you turn a JSONB array into rows in PostgreSQL?

   Use jsonb_array_elements (or jsonb_array_elements_text for plain strings) in the FROM clause, usually with a lateral join. Each array element becomes one row you can filter, join or aggregate.

   IntermediateQuick answer1 min read
5. How do you update a single key inside a JSONB column without replacing the whole document?

   Use jsonb_set(target, path, new_value) or the || concatenation operator to produce a modified copy, and assign it in an UPDATE. PostgreSQL still rewrites the entire jsonb value, since there is no in-place edit.

   IntermediateQuick answer1 min read
6. What does the @> operator do on JSONB and why is it preferred for filtering?

   a @> b is true when jsonb a contains jsonb b, meaning every key and value in b appears in a. It can use a GIN index, whereas filtering with ->> text comparisons usually cannot.

   IntermediateQuick answer1 min read
7. What are the jsonb ? operators and jsonpath queries used for?

   The ? operator tests whether a key or string element exists at the top level; ?| and ?& test any or all of several keys. jsonpath (@? and @@ operators, jsonb_path_query) lets you filter inside nested structures with an expression.

   AdvancedQuick answer1 min read