ในการพัฒนาระบบที่ต้องจัดการกับ tags ซึ่งจัดเก็บในคอลัมน์ประเภท jsonb ของ PostgreSQL เรามักเจอกับโจทย์ที่ต้องค้นหาและจัดอันดับข้อมูลตามจำนวนแท็กที่ตรงกับเงื่อนไขมากที่สุด

หัวใจสำคัญของการทำ Query ลักษณะนี้คือการใช้ LATERAL JOIN ร่วมกับฟังก์ชัน jsonb_array_elements_text() เพื่อแตกอาร์เรย์ใน jsonb ออกมาเป็นแถว (Unnest) สำหรับนำไปเปรียบเทียบและนับจำนวน

Query หลัก: ค้นหาและจัดอันดับ

Query ต่อไปนี้ใช้สำหรับค้นหาเอกสารที่มีแท็กตรงกับ ARRAY[...] ที่กำหนดไว้อย่างน้อย 1 แท็ก และเรียงลำดับตามจำนวนที่ตรงกันมากที่สุด

-- สมมติตารางชื่อ documents มีคอลัมน์ id และ tag_data (jsonb)
-- ค้นหาด้วย tags: 'database', 'sql', 'postgresql'

SELECT
    d.id,
    d.tag_data,
    COUNT(tags.tag) AS matching_tags
FROM
    documents d,
    LATERAL jsonb_array_elements_text(d.tag_data) AS tags(tag)
WHERE
    tags.tag = ANY(ARRAY['database', 'sql', 'postgresql'])
GROUP BY
    d.id, d.tag_data
ORDER BY
    matching_tags DESC;

หลักการทำงาน:

1. LATERAL jsonb_array_elements_text(d.tag_data): แตกสมาชิกทั้งหมดใน tag_data ของแต่ละแถวออกมาเป็น text

1. WHERE tags.tag = ANY(ARRAY[...]): กรองเอาเฉพาะแถว (แท็ก) ที่ตรงกับค่าในอาร์เรย์ที่ต้องการ

1. GROUP BY d.id: จัดกลุ่มผลลัพธ์ตามเอกสารเดิม

1. COUNT(tags.tag): นับจำนวนแท็กที่ผ่านการกรองมาได้ในแต่ละกลุ่ม

1. ORDER BY matching_tags DESC: จัดเรียงผลลัพธ์ตามจำนวนที่นับได้จากมากไปน้อย

กรณีเพิ่มเติม: แสดงผลลัพธ์ทั้งหมด (รวมที่ไม่ตรงเลย)

หากต้องการให้ผลลัพธ์แสดงเอกสารทั้งหมดรวมถึงเอกสารที่ไม่มีแท็กตรงกันเลย (มีค่า matching_tags เป็น 0) ให้ปรับไปใช้ LEFT JOIN และย้ายเงื่อนไขไปไว้ใน ON clause

SELECT
    d.id,
    d.tag_data,
    COUNT(tags.tag) AS matching_tags
FROM
    documents d
LEFT JOIN
    LATERAL jsonb_array_elements_text(d.tag_data) AS tags(tag)
    ON tags.tag = ANY(ARRAY['database', 'sql', 'postgresql'])
GROUP BY
    d.id, d.tag_data
ORDER BY
    matching_tags DESC;