MySQL: return json format

mysql ก็ support json เพราะว่าเดี๋ยวนี้อะไรๆ ก็แลกเปลี่ยนข้อมูลกันโดยใช้ json ถ้าสามารถ query จาก mysql ให้ result ออกมาเป็น json เลย ก็สะดวกที่จะไม่ต้องใช้ java หรือ beanshell ใน joget แปลงผลลัพธ์ให้ยุ่งยาก แค่มี input ซักตัวก็สามารถนำมันไปใช้ได้เลย

เริ่มจากการที่เขียน query ตามปกติเพื่อเลือกข้อมูลที่ต้องการ เช่น

SELECT
    *
FROM
    snippets.province
ORDER BY PROVINCE_NAME ASC;

จะเห็นว่ามี column PROVINCE_ID, PROVINCE_CODE, PROVINCE_NAME และ GEO_ID ทีนี้จะแปลง ให้เป็นรูปแบบ json array โดยใช้ JSON_OBJECT วิธีการใช้คือ

SELECT JSON_OBJECT('name1', value1, 'name2', value2, 'name3', value3);

คือ

SELECT
    JSON_OBJECT('PROVINCE_ID',
            PROVINCE_ID,
            'PROVINCE_CODE',
            PROVINCE_CODE,
            'PROVINCE_NAME',
            PROVINCE_NAME,
            'GEO_ID',
            GEO_ID) AS json
FROM
    snippets.province
ORDER BY PROVINCE_NAME ASC;

ค่าค่อยดูเป็น json ขึ้นมาหน่อย แต่มันยังอยู่คนละแถว ไม่เป็นไรมีตัวช่วย

SELECT
    CONCAT('[',
            GROUP_CONCAT(JSON_OBJECT('PROVINCE_ID',
                        PROVINCE_ID,
                        'PROVINCE_CODE',
                        PROVINCE_CODE,
                        'PROVINCE_NAME',
                        PROVINCE_NAME,
                        'GEO_ID',
                        GEO_ID)),
            ']') AS json
FROM
    snippets.province
ORDER BY PROVINCE_NAME ASC;

แค่นี้ก็ได้ result เป็น json สมใจ อ่านตัวอย่างการใช้ได้ที่ javascript: string to JSON.parse()