Menu
CoddyTech

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:

idcustomer_idamountordered_onstatusmeta
111202025-01-02paid{"channel":"web","items":2,"shipping":{"city":"Haifa"}}
121302025-01-04paid{"channel":"app","items":3,"shipping":{"city":"Haifa"}}
132302025-02-01pending{"channel":"web","items":1}
143502025-02-03paid{"channel":"app","items":5,"shipping":{"city":"Eilat"}}
152102025-02-03paid{"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 icon

Challenge

Medium

Return 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.
quiz iconTest yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Connected Data

Practice on your own: PostgreSQL playground