ในการพัฒนาระบบที่ต้องจัดการกับ tags ซึ่งจัดเก็บในคอลัมน์ประเภท jsonb ของ PostgreSQL เรามักเจอกับโจทย์ที่ต้องค้นหาและจัดอันดับข้อมูลตามจำนวนแท็กที่ตรงกับเงื่อนไขมากที่สุด
หัวใจสำคัญของการทำ Query ลักษณะนี้คือการใช้ LATERAL JOIN ร่วมกับฟังก์ชัน jsonb_array_elements_text() เพื่อแตกอาร์เรย์ใน jsonb ออกมาเป็นแถว (Unnest) สำหรับนำไปเปรียบเทียบและนับจำนวน
Query หลัก: ค้นหาและจัดอันดับ
Query ต่อไปนี้ใช้สำหรับค้นหาเอกสารที่มีแท็กตรงกับ ARRAY[...] ที่กำหนดไว้อย่างน้อย 1 แท็ก และเรียงลำดับตามจำนวนที่ตรงกันมากที่สุด
หลักการทำงาน:
1.
LATERAL jsonb_array_elements_text(d.tag_data): แตกสมาชิกทั้งหมดใน tag_data ของแต่ละแถวออกมาเป็น text2.
WHERE tags.tag = ANY(ARRAY[...]): กรองเอาเฉพาะแถว (แท็ก) ที่ตรงกับค่าในอาร์เรย์ที่ต้องการ3.
GROUP BY d.id: จัดกลุ่มผลลัพธ์ตามเอกสารเดิม4.
COUNT(tags.tag): นับจำนวนแท็กที่ผ่านการกรองมาได้ในแต่ละกลุ่ม5.
ORDER BY matching_tags DESC: จัดเรียงผลลัพธ์ตามจำนวนที่นับได้จากมากไปน้อย
กรณีเพิ่มเติม: แสดงผลลัพธ์ทั้งหมด (รวมที่ไม่ตรงเลย)
หากต้องการให้ผลลัพธ์แสดงเอกสารทั้งหมดรวมถึงเอกสารที่ไม่มีแท็กตรงกันเลย (มีค่า matching_tags เป็น 0) ให้ปรับไปใช้ LEFT JOIN และย้ายเงื่อนไขไปไว้ใน ON clause