Kiểu JSON và JSONB trong PostgreSQL

SELECT '{"b": 1,   "a": 2, "a": 3}'::json  AS as_json,
       '{"b": 1,   "a": 2, "a": 3}'::jsonb AS as_jsonb;

+----------------------------+------------------+
|          as_json           |     as_jsonb     |
+----------------------------+------------------+
| {"b": 1,   "a": 2, "a": 3} | {"a": 3, "b": 1} |
+----------------------------+------------------+
(1 row)

SELECT '{"price": 1.10, "n": 1e2}'::json  AS as_json,
       '{"price": 1.10, "n": 1e2}'::jsonb AS as_jsonb;

+---------------------------+---------------------------+
|          as_json          |         as_jsonb          |
+---------------------------+---------------------------+
| {"price": 1.10, "n": 1e2} | {"n": 100, "price": 1.10} |
+---------------------------+---------------------------+
(1 row)

SELECT '{"a": 1, "b": 2}'::jsonb = '{"b": 2,   "a": 1}'::jsonb AS same_value;

+------------+
| same_value |
+------------+
| t          |
+------------+
(1 row)

CREATE TABLE orders (
  id      integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  payload jsonb NOT NULL
);

INSERT INTO orders (payload) VALUES
  ('{"user_id": 10, "items": [{"sku": "A1", "qty": 2}, {"sku": "B7", "qty": 1}],
     "paid": true, "note": null}'),
  ('{"user_id": 11, "items": [{"sku": "A1", "qty": 5}], "paid": false}'),
  ('{"user_id": 12, "items": [], "paid": true, "coupon": "SAVE5"}');

SELECT id, payload FROM orders ORDER BY id;

+----+----------------------------------------------------------------------------------------------------------+
| id |                                                 payload                                                  |
+----+----------------------------------------------------------------------------------------------------------+
|  1 | {"note": null, "paid": true, "items": [{"qty": 2, "sku": "A1"}, {"qty": 1, "sku": "B7"}], "user_id": 10} |
|  2 | {"paid": false, "items": [{"qty": 5, "sku": "A1"}], "user_id": 11}                                       |
|  3 | {"paid": true, "items": [], "coupon": "SAVE5", "user_id": 12}                                            |
+----+----------------------------------------------------------------------------------------------------------+
(3 rows)

SELECT id,
       payload->'user_id'             AS user_json,
       payload->>'user_id'            AS user_text,
       pg_typeof(payload->>'user_id') AS text_type
FROM orders
ORDER BY id;

+----+-----------+-----------+-----------+
| id | user_json | user_text | text_type |
+----+-----------+-----------+-----------+
|  1 | 10        | 10        | text      |
|  2 | 11        | 11        | text      |
|  3 | 12        | 12        | text      |
+----+-----------+-----------+-----------+
(3 rows)

SELECT id,
       (payload->'items'->0->>'qty')::int AS first_qty,
       payload #>> '{items,0,sku}'        AS first_sku
FROM orders
ORDER BY id;

+----+-----------+-----------+
| id | first_qty | first_sku |
+----+-----------+-----------+
|  1 |         2 | A1        |
|  2 |         5 | A1        |
|  3 |      NULL | NULL      |
+----+-----------+-----------+
(3 rows)

SELECT id,
       -- TODO 1: user_id lấy ra dạng số nguyên, đặt tên cột là user_id
       -- TODO 2: sku của mặt hàng đầu tiên dạng text, đặt tên cột là first_sku
FROM orders
WHERE -- TODO 3: chỉ lấy đơn đã thanh toán, dùng toán tử chứa @>
ORDER BY id;

SELECT '{"a": 1, bad}'::jsonb;

ERROR:  invalid input syntax for type json
LINE 1: SELECT '{"a": 1, bad}'::jsonb;
               ^
DETAIL:  Token "bad" is invalid.
CONTEXT:  JSON data, line 1: {"a": 1, bad...

SELECT '{"a": 1}'::json = '{"a": 1}'::json;

ERROR:  operator does not exist: json = json
LINE 1: SELECT '{"a": 1}'::json = '{"a": 1}'::json;
                                ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

SELECT id FROM orders WHERE payload->>'paid' = true;

ERROR:  operator does not exist: text = boolean
LINE 1: SELECT id FROM orders WHERE payload->>'paid' = true;
                                                     ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

SELECT count(*) FILTER (WHERE payload->>'user_id' > '9')       AS text_gt_9,
       count(*) FILTER (WHERE (payload->>'user_id')::int > 9)  AS int_gt_9
FROM orders;

+-----------+----------+
| text_gt_9 | int_gt_9 |
+-----------+----------+
|         0 |        3 |
+-----------+----------+
(1 row)

SELECT string_agg(id::text, ', ' ORDER BY id) FILTER (WHERE payload->>'paid' = 'true')       AS text_compare,
       string_agg(id::text, ', ' ORDER BY id) FILTER (WHERE (payload->>'paid')::boolean)     AS cast_compare
FROM orders;

+--------------+--------------+
| text_compare | cast_compare |
+--------------+--------------+
| 1, 3         | 1, 3         |
+--------------+--------------+
(1 row)

SELECT string_agg(id::text, ', ' ORDER BY id)
         FILTER (WHERE payload @> '{"paid": true}')                AS paid_orders,
       string_agg(id::text, ', ' ORDER BY id)
         FILTER (WHERE payload @> '{"items": [{"sku": "A1"}]}')    AS has_sku_a1,
       string_agg(id::text, ', ' ORDER BY id)
         FILTER (WHERE payload ? 'coupon')                         AS has_coupon
FROM orders;

+-------------+------------+------------+
| paid_orders | has_sku_a1 | has_coupon |
+-------------+------------+------------+
| 1, 3        | 1, 2       | 3          |
+-------------+------------+------------+
(1 row)

SELECT id,
       payload->'note'            AS note_json,
       payload->>'note'           AS note_text,
       payload->'note' IS NULL    AS note_is_sql_null,
       payload->'missing' IS NULL AS missing_is_sql_null
FROM orders
ORDER BY id;

+----+-----------+-----------+------------------+---------------------+
| id | note_json | note_text | note_is_sql_null | missing_is_sql_null |
+----+-----------+-----------+------------------+---------------------+
|  1 | null      | NULL      | f                | t                   |
|  2 | NULL      | NULL      | t                | t                   |
|  3 | NULL      | NULL      | t                | t                   |
+----+-----------+-----------+------------------+---------------------+
(3 rows)

CREATE TABLE json_docs (doc json);
CREATE INDEX idx_json_docs ON json_docs USING gin (doc);

ERROR:  data type json has no default operator class for access method "gin"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

CREATE TABLE big_orders (
  id      integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  payload jsonb NOT NULL
);

INSERT INTO big_orders (payload)
SELECT jsonb_build_object(
         'user_id', g % 5000,
         'paid', g % 3 = 0,
         'items', jsonb_build_array(jsonb_build_object('sku', 'S' || (g % 400), 'qty', 1 + g % 5)))
FROM generate_series(1, 50000) AS g;

CREATE INDEX idx_big_gin ON big_orders USING gin (payload);
ANALYZE big_orders;

EXPLAIN (COSTS OFF) SELECT id FROM big_orders WHERE payload @> '{"user_id": 42}';

EXPLAIN (COSTS OFF) SELECT id FROM big_orders WHERE payload->>'user_id' = '42';

+-----------------------------------------------------------+
|                        QUERY PLAN                         |
+-----------------------------------------------------------+
| Bitmap Heap Scan on big_orders                            |
|   Recheck Cond: (payload @> '{"user_id": 42}'::jsonb)     |
|   ->  Bitmap Index Scan on idx_big_gin                    |
|         Index Cond: (payload @> '{"user_id": 42}'::jsonb) |
+-----------------------------------------------------------+
(4 rows)

+--------------------------------------------------------+
|                       QUERY PLAN                       |
+--------------------------------------------------------+
| Seq Scan on big_orders                                 |
|   Filter: ((payload ->> 'user_id'::text) = '42'::text) |
+--------------------------------------------------------+
(2 rows)

CREATE INDEX idx_big_user ON big_orders ((payload->>'user_id'));
ANALYZE big_orders;

EXPLAIN (COSTS OFF) SELECT id FROM big_orders WHERE payload->>'user_id' = '42';

EXPLAIN (COSTS OFF) SELECT id FROM big_orders WHERE (payload->>'user_id')::int = 42;

+------------------------------------------------------------------+
|                            QUERY PLAN                            |
+------------------------------------------------------------------+
| Bitmap Heap Scan on big_orders                                   |
|   Recheck Cond: ((payload ->> 'user_id'::text) = '42'::text)     |
|   ->  Bitmap Index Scan on idx_big_user                          |
|         Index Cond: ((payload ->> 'user_id'::text) = '42'::text) |
+------------------------------------------------------------------+
(4 rows)

+-----------------------------------------------------------+
|                        QUERY PLAN                         |
+-----------------------------------------------------------+
| Seq Scan on big_orders                                    |
|   Filter: (((payload ->> 'user_id'::text))::integer = 42) |
+-----------------------------------------------------------+
(2 rows)

CREATE INDEX idx_big_pp ON big_orders USING gin (payload jsonb_path_ops);

SELECT pg_size_pretty(pg_relation_size('idx_big_gin')) AS default_gin,
       pg_size_pretty(pg_relation_size('idx_big_pp'))  AS path_ops_gin;

+-------------+--------------+
| default_gin | path_ops_gin |
+-------------+--------------+
| 1312 kB     | 1000 kB      |
+-------------+--------------+
(1 row)

SELECT 'set' AS op, jsonb_set(payload, '{paid}', 'true') AS result
FROM orders WHERE id = 2
UNION ALL
SELECT 'minus', payload - 'items' FROM orders WHERE id = 2
UNION ALL
SELECT 'merge', payload || '{"status": "ok"}' FROM orders WHERE id = 2;

+-------+------------------------------------------------------------------------------------+
|  op   |                                       result                                       |
+-------+------------------------------------------------------------------------------------+
| set   | {"paid": true, "items": [{"qty": 5, "sku": "A1"}], "user_id": 11}                  |
| minus | {"paid": false, "user_id": 11}                                                     |
| merge | {"paid": false, "items": [{"qty": 5, "sku": "A1"}], "status": "ok", "user_id": 11} |
+-------+------------------------------------------------------------------------------------+
(3 rows)

SELECT id,
       (payload->>'user_id')::int AS user_id,
       payload #>> '{items,0,sku}' AS first_sku
FROM orders
WHERE payload @> '{"paid": true}'
ORDER BY id;

+----+---------+-----------+
| id | user_id | first_sku |
+----+---------+-----------+
|  1 |      10 | A1        |
|  3 |      12 | NULL      |
+----+---------+-----------+
(2 rows)

SELECT payload->'paid'                  AS e1,
       pg_typeof(payload->>'paid')      AS e2,
       payload->'items'->0->'sku'       AS e3,
       payload->>'missing'              AS e4,
       jsonb_array_length(payload->'items') AS e5
FROM orders
WHERE id = 1;

+------+------+------+------+----+
|  e1  |  e2  |  e3  |  e4  | e5 |
+------+------+------+------+----+
| true | text | "A1" | NULL |  2 |
+------+------+------+------+----+
(1 row)

SELECT '{"a": 1, "a": 2}'::json  AS as_json,
       '{"a": 1, "a": 2}'::jsonb AS as_jsonb,
       ('{"a": 1, "a": 2}'::json)->>'a' AS json_pick;

+------------------+----------+-----------+
|     as_json      | as_jsonb | json_pick |
+------------------+----------+-----------+
| {"a": 1, "a": 2} | {"a": 2} | 2         |
+------------------+----------+-----------+
(1 row)

UPDATE orders
SET payload = jsonb_set(payload, '{paid}', 'true') || '{"coupon": "SAVE10"}'
WHERE id = 2;

SELECT id, payload->>'paid' AS paid, payload->>'coupon' AS coupon
FROM orders
ORDER BY id;

+----+------+--------+
| id | paid | coupon |
+----+------+--------+
|  1 | true | NULL   |
|  2 | true | SAVE10 |
|  3 | true | SAVE5  |
+----+------+--------+
(3 rows)