postgres_jsonb_cheat_sheet.sql

-- 1. Select items by the value of a first level attribute (#1 way)
SELECT * FROM users WHERE metadata @> '{"country": "Peru"}'; 

-- 2. Select items by the value of a first level attribute (#2 way)
SELECT * FROM users WHERE metadata->>'country' = 'Peru';

-- 3. 3. Select item attribute value
SELECT metadata->>'country' FROM users;

-- 4. Select only items where a particular attribute is present
SELECT * FROM users WHERE metadata->>'country' IS NOT NULL;

-- 5. Select items by the value of a nested attribute
SELECT * FROM users WHERE metadata->'company'->>'name' = "Mozilla";
SELECT * 
  FROM users 
  WHERE metadata @> '{"company":{"name": "Mozilla"}}';

-- 6. Select items by the value of an attribute in an array
SELECT * FROM users WHERE metadata @> '{"companies": ["Mozilla"]}';

-- 7. IN operator on attributes
SELECT * FROM users 
  WHERE metadata->>'countries' IN ('Chad', 'Japan');

-- 8. Insert a whole object
UPDATE users SET metadata = '{"country": "India"}';

-- 9. Update or insert an attribute
UPDATE users SET metadata = metadata || '{"country": "Egypt"}';

-- 10. Removing an attribute
UPDATE users SET metadata = metadata - 'country';


-- to read more look at: https://medium.com/hackernoon/how-to-query-jsonb-beginner-sheet-cheat-4da3aa5082a3