JSONB في PostgreSQL: إمتى تستخدمه، وإزاي تسأله وتعمله index

DROP TABLE IF EXISTS products CASCADE;
CREATE TABLE products (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL,
  attrs jsonb NOT NULL DEFAULT '{}'
);
INSERT INTO products (name, attrs) VALUES
  ('laptop', '{"ram": 16, "cpu": {"brand": "amd", "cores": 8}, "tags": ["dev", "sale"]}'),
  ('shirt',  '{"size": "L", "color": "blue", "tags": ["sale"]}'),
  ('book',   '{"pages": 320, "lang": "ar"}');

-- -> بيرجّع jsonb، و ->> بيرجّع text
SELECT name, attrs -> 'cpu' AS cpu_json, attrs -> 'cpu' ->> 'brand' AS brand,
       (attrs ->> 'ram')::int AS ram
FROM products;

-- @> يعني «فيه»، و ? يعني «الـ key موجود»
SELECT name FROM products WHERE attrs @> '{"tags": ["sale"]}';
SELECT name FROM products WHERE attrs ? 'size';

-- تعديل جزء بـ jsonb_set، وإضافة بـ ||
UPDATE products SET attrs = jsonb_set(attrs, '{cpu,cores}', '12') WHERE name = 'laptop';
UPDATE products SET attrs = attrs || '{"stock": 5}' WHERE name = 'book';
SELECT name, attrs FROM products ORDER BY id;

$ psql -q -f schema.sql
  name  |           cpu_json           | brand | ram 
--------+------------------------------+-------+-----
 laptop | {"brand": "amd", "cores": 8} | amd   |  16
 shirt  |                              |       |    
 book   |                              |       |    
(3 rows)

  name  
--------
 laptop
 shirt
(2 rows)

 name  
-------
 shirt
(1 row)

  name  |                                   attrs                                    
--------+----------------------------------------------------------------------------
 laptop | {"cpu": {"brand": "amd", "cores": 12}, "ram": 16, "tags": ["dev", "sale"]}
 shirt  | {"size": "L", "tags": ["sale"], "color": "blue"}
 book   | {"lang": "ar", "pages": 320, "stock": 5}
(3 rows)

DROP TABLE IF EXISTS events CASCADE;
CREATE TABLE events (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload jsonb NOT NULL
    CHECK (jsonb_typeof(payload) = 'object' AND payload ? 'type')
);
INSERT INTO events (payload)
SELECT jsonb_build_object(
  'type', CASE WHEN i % 50 = 0 THEN 'refund' ELSE 'payment' END,
  'user', i % 1000,
  'amount', (i * 7) % 900
) FROM generate_series(1, 50000) AS i;

-- CHECK بيمنع JSON مالوش type
INSERT INTO events (payload) VALUES ('{"amount": 5}');

CREATE INDEX events_payload_gin ON events USING gin (payload jsonb_path_ops);
ANALYZE events;

EXPLAIN (COSTS OFF) SELECT count(*) FROM events WHERE payload @> '{"type": "refund"}';
SELECT count(*) FROM events WHERE payload @> '{"type": "refund"}';

-- ->> مع = ما بيستخدمش الـ GIN index
EXPLAIN (COSTS OFF) SELECT count(*) FROM events WHERE payload ->> 'type' = 'refund';

$ psql -q -f schema.sql
psql:schema.sql:15: ERROR:  new row for relation "events" violates check constraint "events_payload_check"
DETAIL:  Failing row contains (50001, {"amount": 5}).
                             QUERY PLAN                             
--------------------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on events
         Recheck Cond: (payload @> '{"type": "refund"}'::jsonb)
         ->  Bitmap Index Scan on events_payload_gin
               Index Cond: (payload @> '{"type": "refund"}'::jsonb)
(5 rows)

 count 
-------
  1000
(1 row)

                          QUERY PLAN                           
---------------------------------------------------------------
 Aggregate
   ->  Seq Scan on events
         Filter: ((payload ->> 'type'::text) = 'refund'::text)
(3 rows)

-- webhooks جاية من بوابة دفع: بنخزنها زي ما هي
DROP TABLE IF EXISTS webhook_events CASCADE;
CREATE TABLE webhook_events (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  received_at timestamptz NOT NULL DEFAULT now(),
  payload     jsonb NOT NULL
);
INSERT INTO webhook_events (payload) VALUES
  ('{"type": "payment.succeeded", "data": {"order": 7, "amount": 19950, "customer": {"email": "[email protected]"}}}'),
  ('{"type": "payment.failed", "data": {"order": 8, "amount": 5000, "reason": "insufficient_funds"}}'),
  ('{"type": "payment.succeeded", "data": {"order": 9, "amount": 30000, "customer": {"email": "[email protected]"}}}'),
  ('{"type": "refund.created", "data": {"order": 7, "amount": 19950}}');


SELECT payload ->> 'type' AS type, count(*) FROM webhook_events GROUP BY 1 ORDER BY 1;

SELECT sum((payload -> 'data' ->> 'amount')::int) / 100 AS succeeded_egp
FROM webhook_events WHERE payload @> '{"type": "payment.succeeded"}';

SELECT payload #>> '{data,customer,email}' AS email
FROM webhook_events WHERE payload @> '{"type": "payment.succeeded"}' ORDER BY id;

ALTER TABLE webhook_events
  ADD COLUMN order_id int GENERATED ALWAYS AS ((payload -> 'data' ->> 'order')::int) STORED;
CREATE INDEX webhook_events_order_id ON webhook_events (order_id);

SELECT id, payload ->> 'type' AS type FROM webhook_events WHERE order_id = 7 ORDER BY id;

$ psql -q -f schema.sql
       type        | count 
-------------------+-------
 payment.failed    |     1
 payment.succeeded |     2
 refund.created    |     1
(3 rows)

 succeeded_egp 
---------------
           499
(1 row)

   email    
------------
 [email protected]
 [email protected]
(2 rows)

 id |       type        
----+-------------------
  1 | payment.succeeded
  4 | refund.created
(2 rows)