Beginner LevelPostgreSQL
How do JSONB query operators (@>, ->, ->>, ?|) extract and filter document fields?
❓ Interview Prompt: Explain JSONB query operators: -> (returns JSON), ->> (returns text), @> (contains JSONB), ? (key exists), and GIN indexing.
PostgreSQL provides rich operators for manipulating JSONB data:
•
'->': Extracts JSON object/array element by key or index.
•
'->>': Extracts field value as plain 'TEXT'.
•
'@>': Containment operator (checks if left JSONB contains right JSONB structure).
•
'?': Checks if a top-level key string exists inside JSONB.
Code Implementation Example
sql
-- Filter users where attributes JSONB contains active status
SELECT name, attributes->>'department' AS dept
FROM user_profiles
WHERE attributes @> '{"status": "ACTIVE"}';
-- Create GIN index to accelerate @> containment queries
CREATE INDEX idx_user_attributes_gin ON user_profiles USING gin (attributes);💡Querying JSONB documents using containment (@>) and arrow (->>) operators.
🎯 Interview Key Takeaway
Use @> with a GIN index for high-performance JSONB containment queries.
#JSONB#Operators#@>#GIN Index#Beginner
Back to all PostgreSQL Questions→