[Solved] How can you find rows where a JSONB column data contains a nested JSON object {"user":{"id":123}}? — Ans: SELECT * FROM table WHERE data @> '{"user":{"id":123}}'::jsonb; | PostgreSQL

PostgreSQL - JSON and JSONB

How can you find rows where a JSONB column data contains a nested JSON object {"user":{"id":123}}?

ASELECT * FROM table WHERE data @> '{"user":{"id":123}}'::jsonb;

BSELECT * FROM table WHERE data @> '{"user.id":123}'::jsonb;

CSELECT * FROM table WHERE data ? 'user.id';

DSELECT * FROM table WHERE data @> '{"id":123}'::jsonb;

Step-by-Step Solution

Solution:

Step 1: Use nested JSON structure in containment
The JSONB containment operator supports nested objects, so the nested JSON must be written as is.
Step 2: Eliminate incorrect options
Dot notation like {"user.id":123} is invalid JSON, the ? operator checks keys only, and {"id":123} checks only top-level key.
Final Answer:
Use nested JSON object in @> operator as in option A -> Option A
Quick Check:
Nested JSONB containment uses nested JSON syntax = A [OK]

Quick Trick: Write nested JSON exactly for @> containment [OK]

Common Mistakes:

Master "JSON and JSONB" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

How can you find rows where a JSONB column data contains a nested JSON object {"user":{"id":123}}?