Interview Q&A / PostgreSQL

1. 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
2. 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
3. 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
4. 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