JSONB operators & JSONPath — ->, ->>, @>, ?, jsonb_path_query

task_metadata = '{"priority": "high", "labels": ["bug","prod"], "estimate": {"hours": 4, "complexity": "M"}}'

-- KHONG CHAY DUOC:
SELECT * FROM tasks WHERE task_metadata.priority = 'high';
-- ERROR: column "task_metadata.priority" does not exist

-- Schema tham khao
-- tasks.task_metadata JSONB, vi du row:
-- '{"priority": "high", "labels": ["bug","prod"], "estimate": {"hours": 4}}'

-- -> tra ve JSONB (co dau ngoac kep khi la string)
SELECT task_metadata->'priority'
FROM tasks WHERE id = 1;
-- Ket qua: "high"   (jsonb string, co dau ngoac kep)

-- ->> tra ve TEXT (khong co dau ngoac kep)
SELECT task_metadata->>'priority'
FROM tasks WHERE id = 1;
-- Ket qua: high   (text, khong ngoac kep)

-- So sanh dung ->> de lay text
SELECT title, task_metadata->>'priority' AS priority
FROM tasks
WHERE task_metadata->>'priority' = 'high';

-- -> dung de query tiep vao ket qua (chuoi operator)
SELECT task_metadata->'estimate'->'hours'
FROM tasks WHERE id = 1;
-- Ket qua: 4  (jsonb number)

-- ->> de lay text tu array element theo index (0-based)
SELECT task_metadata->'labels'->>0
FROM tasks WHERE id = 1;
-- Ket qua: bug  (text, phan tu dau tien cua array)

-- Sai: chain tren text (ERROR)
SELECT task_metadata->>'estimate'->>'hours'
FROM tasks WHERE id = 1;
-- ERROR: operator does not exist: text ->> unknown
-- Giai thich: ->> tra text, text khong co operator ->>

-- Dung: chain tren jsonb, lay text o cuoi
SELECT task_metadata->'estimate'->>'hours'
FROM tasks WHERE id = 1;
-- OK: tra ve text "4"

-- #> lay nested field, tra ve JSONB
SELECT task_metadata #> '{estimate,hours}'
FROM tasks WHERE id = 1;
-- Ket qua: 4  (jsonb)

-- #>> lay nested field, tra ve TEXT
SELECT task_metadata #>> '{estimate,hours}'
FROM tasks WHERE id = 1;
-- Ket qua: 4  (text)

-- Equivalent voi chain -> -> ->>:
SELECT task_metadata->'estimate'->>'hours'
FROM tasks WHERE id = 1;
-- Ket qua: 4  (text) -- giong nhu #>>

-- #>> voi array index (0-based) va path khong ton tai:
SELECT task_metadata #>> '{labels,0}' FROM tasks WHERE id = 1;
-- Ket qua: bug  (text, phan tu dau tien)

SELECT task_metadata #>> '{nonexistent,field}' FROM tasks WHERE id = 1;
-- Ket qua: NULL  (khong raise error)

-- Hai cach viet tuong duong (chon #>> khi path sau 3+ cap):
-- task_metadata->'level1'->'level2'->>'level3'
-- task_metadata #>> '{level1,level2,level3}'

-- @> check: left chua right
-- Filter task co priority = high
SELECT title
FROM tasks
WHERE task_metadata @> '{"priority": "high"}';

-- Filter task co label "prod" trong array
SELECT title
FROM tasks
WHERE task_metadata @> '{"labels": ["prod"]}';
-- Luu y: containment array, khong phai exact match
-- {"labels": ["bug","prod"]} @> '{"labels": ["prod"]}' -> TRUE
-- {"labels": ["bug","prod"]} @> '{"labels": ["prod","bug"]}' -> TRUE (thu tu khong quan trong)

-- Nhieu field cung luc (AND semantic)
SELECT title
FROM tasks
WHERE task_metadata @> '{"priority": "high", "estimate": {"complexity": "M"}}';
-- Tat ca field trong right phai co trong left

-- <@ la mirror (it dung hon): left la subset cua right
SELECT '{"a": 1}'::jsonb <@ '{"a": 1, "b": 2}'::jsonb;
-- TRUE: {"a":1} subset cua {"a":1,"b":2}

-- ? kiem tra mot key ton tai o top-level
SELECT title
FROM tasks
WHERE task_metadata ? 'compliance_tag';
-- Tim tat ca task co field compliance_tag (bat ky value nao)

-- ?| kiem tra it nhat mot trong cac key ton tai (OR)
SELECT title
FROM tasks
WHERE task_metadata ?| ARRAY['compliance_tag', 'gdpr_flag', 'pii_data'];
-- Task co bat ky truong nao trong so nay

-- ?& kiem tra TAT CA cac key ton tai (AND)
SELECT title
FROM tasks
WHERE task_metadata ?& ARRAY['priority', 'estimate', 'labels'];
-- Chi task co du ca ba field nay

-- Ket hop: task co compliance_tag VA priority = high
SELECT title
FROM tasks
WHERE task_metadata ? 'compliance_tag'
  AND task_metadata->>'priority' = 'high';

-- ? chi check top-level key, khong check nested
SELECT '{"estimate": {"hours": 4}}'::jsonb ? 'hours';
-- FALSE: 'hours' la nested, khong phai top-level

SELECT '{"estimate": {"hours": 4}}'::jsonb ? 'estimate';
-- TRUE: 'estimate' la top-level key

-- jsonb_path_exists: tra ve boolean (giong WHERE clause)
SELECT jsonb_path_exists(
  task_metadata,
  '$.priority == "high"'
)
FROM tasks WHERE id = 1;
-- TRUE neu priority la "high"

-- jsonb_path_query: tra ve tap ket qua (SET OF jsonb)
SELECT jsonb_path_query(
  task_metadata,
  '$.labels[*]'
)
FROM tasks WHERE id = 1;
-- Tra ve tung element cua array labels:
-- "bug"
-- "prod"

-- jsonb_path_query_first: chi lay ket qua dau tien
SELECT jsonb_path_query_first(
  task_metadata,
  '$.labels[0]'
)
FROM tasks WHERE id = 1;
-- "bug"

-- Filter array element theo predicate
-- Tim tat ca task co bat ky label nao la "prod"
SELECT title
FROM tasks
WHERE jsonb_path_exists(
  task_metadata,
  '$.labels[*] ? (@ == "prod")'
);

-- JSONPath syntax chinh:
-- $.field         : top-level field
-- $.a.b           : nested field
-- $.arr[*]        : tat ca element cua array
-- $.arr[0]        : element theo index (0-based)
-- $.arr[*] ? (@ > 5)       : filter element lon hon 5
-- $.tasks[*] ? (@.priority == "high")  : filter object trong array

-- SAI: so sanh text, '9' > '10' theo ASCII (dung ve text, sai ve so hoc)
WHERE task_metadata->>'score' > '9'

-- DUNG: cast sang numeric
WHERE (task_metadata->>'score')::numeric > 9

-- JSONPath so sanh so truc tiep, khong can cast:
WHERE jsonb_path_exists(task_metadata, '$.estimate.hours > 3')

-- Boolean: ->> tra text 'true'/'false', can cast
WHERE (task_metadata->>'is_archived')::boolean = false

-- Hoac dung -> voi jsonb literal
WHERE task_metadata->'is_archived' = 'false'::jsonb

-- Filter GDPR task chua done trong 7 ngay
SELECT t.id, t.title, t.task_metadata->>'reviewer_count' AS reviewers
FROM tasks t
WHERE t.task_metadata @> '{"compliance_tag": "GDPR"}'
  AND t.status != 'done'
  AND t.created_at > now() - INTERVAL '7 days'
ORDER BY t.created_at DESC;

-- Aggregate: dem task theo priority, tinh trung binh gio
SELECT task_metadata->>'priority'                          AS priority,
       COUNT(*)                                            AS task_count,
       AVG((task_metadata->>'estimate_hours')::numeric)   AS avg_hours
FROM tasks
WHERE task_metadata ? 'priority'   -- bo qua row khong co field
GROUP BY task_metadata->>'priority'
ORDER BY task_count DESC;

-- jsonb_set(target, path, new_value, create_missing)
UPDATE tasks
SET task_metadata = jsonb_set(task_metadata, '{estimate,hours}', '6'::jsonb, true)
WHERE id = 1;

-- Merge / them field moi: || operator
UPDATE tasks
SET task_metadata = task_metadata || '{"reviewed_at": "2025-05-05"}'::jsonb
WHERE id = 1;
-- Key trung lap: gia tri tu right thang

-- Xoa key: - operator
UPDATE tasks
SET task_metadata = task_metadata - 'temp_flag'
WHERE task_metadata ? 'temp_flag';