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;
-- "high"   (jsonb string)

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

-- ->> de so sanh trong WHERE
SELECT title, task_metadata->>'priority' AS priority
FROM tasks
WHERE task_metadata->>'priority' = 'high';

-- -> de chain tiep
SELECT task_metadata->'estimate'->'hours' FROM tasks WHERE id = 1;
-- 4  (jsonb number)

-- ->> voi array index (0-based)
SELECT task_metadata->'labels'->>0 FROM tasks WHERE id = 1;
-- bug

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

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

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

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

-- Array index trong path
SELECT task_metadata #>> '{labels,0}' FROM tasks WHERE id = 1;
-- bug

-- Path khong ton tai: NULL, khong raise error
SELECT task_metadata #>> '{nonexistent,field}' FROM tasks WHERE id = 1;
-- NULL

-- 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"]}';
-- 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"}}';

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

-- ? mot key ton tai o top-level
SELECT title FROM tasks WHERE task_metadata ? 'compliance_tag';

-- ?| it nhat mot trong cac key (OR)
SELECT title FROM tasks
WHERE task_metadata ?| ARRAY['compliance_tag', 'gdpr_flag', 'pii_data'];

-- ?& TAT CA cac key (AND)
SELECT title FROM tasks
WHERE task_metadata ?& ARRAY['priority', 'estimate', 'labels'];

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

SELECT '{"estimate": {"hours": 4}}'::jsonb ? 'estimate';
-- TRUE

-- jsonb_path_match (hoac toan tu @@): predicate tra ve boolean
SELECT jsonb_path_match(task_metadata, '$.priority == "high"')
FROM tasks WHERE id = 1;
-- true

-- jsonb_path_exists (hoac @?): path co tra ve item nao khong
SELECT jsonb_path_exists(task_metadata, '$.estimate.hours')
FROM tasks WHERE id = 1;
-- true

-- jsonb_path_query: tra ve SET OF jsonb
SELECT jsonb_path_query(task_metadata, '$.labels[*]')
FROM tasks WHERE id = 1;
-- "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
SELECT title FROM tasks
WHERE jsonb_path_exists(task_metadata, '$.labels[*] ? (@ == "prod")');

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

-- So sanh text: '10' < '9' theo bang chu cai
WHERE task_metadata->>'score' > '9'

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

-- JSONPath hieu so la so, khong can cast
WHERE task_metadata @@ '$.estimate.hours > 3'

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

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

-- Aggregate theo JSONB field
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;

-- Update nested field: 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: || (key trung -> gia tri ben phai thang)
UPDATE tasks
SET task_metadata = task_metadata || '{"reviewed_at": "2025-05-05"}'::jsonb
WHERE id = 1;

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