Match JSONB Structure
Part of the Connected Data section of Coddy's PostgreSQL journey. Lesson 19 of 25.
The JSONB containment operator @> asks whether the left value contains the structure on the right. Object key order does not matter. JSON value types do matter: the number 2 differs from the string 2.
Input table orders:
id | customer_id | amount | ordered_on | status | meta |
|---|---|---|---|---|---|
11 | 1 | 20 | 2025-01-02 | paid | {"channel":"web","items":2,"shipping":{"city":"Haifa"}} |
12 | 1 | 30 | 2025-01-04 | paid | {"channel":"app","items":3,"shipping":{"city":"Haifa"}} |
13 | 2 | 30 | 2025-02-01 | pending | {"channel":"web","items":1} |
14 | 3 | 50 | 2025-02-03 | paid | {"channel":"app","items":5,"shipping":{"city":"Eilat"}} |
15 | 2 | 10 | 2025-02-03 | paid | {"channel":"web","items":2} |
SELECT id FROM orders WHERE meta::jsonb @> '{"channel":"web","items":2}'::jsonb ORDER BY id;Orders 11 and 15 contain both requested fields, even though order 11 also has shipping. Containment does not require whole-document equality. A right-side numeric value must match a JSON number.
Use @> to test a partial JSONB structure while allowing extra fields.
Challenge
MediumReturn id and amount for orders whose JSON contains channel web and items 1. Use JSONB containment and order by id.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in Connected Data
1Connect Tables
Match Rows with JOINKeep Rows with LEFT JOINCount After a JoinFind Missing MatchesCustomer Coverage4Work with JSONB
Read JSONB FieldsNavigate Nested JSONConvert JSON NumbersMatch JSONB StructureWeb Shipping AuditPractice on your own: PostgreSQL playground