Tag Archive อาร์เรย์

Byphunsanit

MySQL: select ข้อมูลจาก json array

Mysql การดึงข้อมูลจากรูปแบบ json ที่เก็บเป็น array ไว้ จะเขียน query ซับซ้อนกว่าปกตินิดหนึ่งเพราะว่าต้องมีการดึงข้อมูลโดยทราบจำนวนแถว เช่น จากข้อมูล

CREATE TABLE `app_fd_books` (
  `id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `dateCreated` datetime DEFAULT NULL,
  `dateModified` datetime DEFAULT NULL,
  `c_books` longtext COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `app_fd_books` (`id`,`dateCreated`,`dateModified`,`c_books`) VALUES ('b04469bd-ac1430f1-12bd2000-a4f7da3a','2018-03-03 22:54:56','2018-03-03 22:54:56','[{\n		\"title\": \"สูญสิ้นความเป็นคน\",\n		\"author\": \"ดะไซ โอซามุ\",\n		\"publisher\": \"JLIT\"\n	}, {\n		\"title\": \"ถอดรหัสรักออนไลน์\",\n		\"author\": \"Aziz Ansari\",\n		\"publisher\": \"openworlds\"\n	},\n	{\n		\"title\": \"เพลงรัตติกาลในอินเดีย\",\n		\"author\": \"Antonio Tabucchi\",\n		\"publisher\": \"อ่านอิตาลี\"\n	},\n	{\n		\"title\": \"อันเดอร์กราวด์\",\n		\"author\": \"ผู้เขียน: ฮารูกิ มูราคามิ\",\n		\"publisher\": \"กำมะหยี่\"\n	},\n	{\n		\"title\": \"ร้านหนังสือเลขที่ 84 ถนนแชริงครอสส์\",\n		\"author\": \"Helene Hanff\",\n		\"publisher\": \"Bookmoby Press\"\n	},\n	{\n		\"title\": \"ปรัชญาในภาพยนตร์\",\n		\"author\": \"ผู้เขียน: สิตางค์ เจริญวงศ์ และ ฐานชน จันทร์เรือง\",\n		\"publisher\": \"ปล่อยสำนักพิมพ์\"\n	},\n	{\n		\"title\": \"ผู้มาเยือนหลังเที่ยงคืน\",\n		\"author\": \"แพทริก เนส\",\n		\"publisher\": \"เวิร์ดส์วอนเดอร์\"\n	},\n	{\n		\"title\": \"เข้าป่าหาชีวิต\",\n		\"author\": \"จอน คราคาวเออร์\",\n		\"publisher\": \"มูลนิธิหนังสือเพื่อสังคม\"\n	},\n	{\n		\"title\": \"ไกลกว่ารั้วบ้านของเรา\",\n		\"author\": \"โรสนี นูรฟารีดา\",\n		\"publisher\": \"ผจญภัย\"\n	},\n	{\n		\"title\": \"สัจนิยมมหัศจรรย์\",\n		\"author\": \"ชูศักดิ์ ภัทรกุลวณิชย์\",\n		\"publisher\": \"อ่าน\"\n	},\n	{\n		\"title\": \"เข้าป่าหาชีวิต\",\n		\"author\": \"จอน คราคาวเออร์\",\n		\"publisher\": \"มูลนิธิหนังสือเพื่อสังคม\"\n	},\n	{\n		\"title\": \"ความลับ 5 ข้อที่คุณต้องค้นให้พบก่อนตาย\",\n		\"author\": \"จอห์น อิซโซ\",\n		\"publisher\": \"โอ้มายก้อด\"\n	},\n	{\n		\"title\": \"ฝนบางหยด กลายเป็นผีเสื้อ\",\n		\"author\": \"ลัดดา สงกระสินธิ์\",\n		\"publisher\": \"สมมติ\"\n	},\n	{\n		\"title\": \"Open Diary\",\n		\"author\": \"วรพจน์ พันธุ์พงศ์\",\n		\"publisher\": \"บางลำพู\"\n	},\n	{\n		\"title\": \"A Gothic Soul\",\n		\"author\": \"Jiří Karásek ze Lvovic\",\n		\"publisher\": \"Twisted Spoon\"\n	},\n	{\n		\"title\": \"ไกลกว่ารั้วบ้านของเรา\",\n		\"author\": \"โรสนี นูรฟารีดา\",\n		\"publisher\": \"ผจญภัย\"\n	},\n	{\n		\"title\": \"ประวัติศาสตร์นับศูนย์: สู่การสูญพันธุ์ครั้งที่ 6\",\n		\"author\": \"เอลิซาเบธ โคลเบิร์ต\",\n		\"publisher\": \"openworlds\"\n	},\n	{\n		\"title\": \"FUTURE : ปัญญาอนาคต\",\n		\"author\": \"ภิญโญ ไตรสุริยธรรมา\",\n		\"publisher\": \"openbooks\"\n	}\n]');

c_books มีจำนวนข้อมูลที่เก็บเอาไว้ 18 รายการ

เพื่อความสดวกการเขียน query จะใช้วิธีสร้างตารางเก็บ index ไว้ให้เกาะโดย เราจะสร้างตาราง integers ที่เก็บจำนวนเต็มตั้งแต่ 0 ถึง n ( function JSON_EXTRACT เริ่มจาก index 0 ) เอาไว้ให้ใช้ง่ายๆ เช่น

CREATE TABLE `integers` (
`row_number` int(4) NOT NULL,
PRIMARY KEY (`row_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO integers (row_number) VALUES ('0') , ('1') , ('2') , ('3') , ('4') , ('5') , ('6') , ('7') , ('8') , ('9') , ('10') , ('11') , ('12') , ('13') , ('14') , ('15') , ('16') , ('17') , ('18') , ('19') , ('20') , ('21') , ('22') , ('23') , ('24') , ('25') , ('26') , ('27') , ('28') , ('29') , ('30') , ('31') , ('32') , ('33') , ('34') , ('35') , ('36') , ('37') , ('38') , ('39') , ('40') , ('41') , ('42') , ('43') , ('44') , ('45') , ('46') , ('47') , ('48') , ('49') , ('50') , ('51') , ('52') , ('53') , ('54') , ('55') , ('56') , ('57') , ('58') , ('59') , ('60') , ('61') , ('62') , ('63') , ('64') , ('65') , ('66') , ('67') , ('68') , ('69') , ('70') , ('71') , ('72') , ('73') , ('74') , ('75') , ('76') , ('77') , ('78') , ('79') , ('80') , ('81') , ('82') , ('83') , ('84') , ('85') , ('86') , ('87') , ('88') , ('89') , ('90') , ('91') , ('92') , ('93') , ('94') , ('95') , ('96') , ('97') , ('98') , ('99') , ('100')

ส่วนเรื่องจะดึงข้อมูลถึงอันดับที่เท่าไหร่ ถ้าไม่บันทึกไว้ที่คอลัมน์อื่น ก็ทำได้โดยการเขียนเงื่อนไขเช่น ข้อมูลเกี่ยวกับหนังสือทุกเล่ม ต้องมีชื่อเรื่อง (title) ก็จะเขียนประมาณ

AND (SELECT
            TRIM(BOTH '"' FROM JSON_EXTRACT(datas.c_books,
                            CONCAT('$[', row_number, '].title')))
        FROM
            app_fd_books
        WHERE
            id = 'b04469bd-ac1430f1-12bd2000-a4f7da3a') IS NOT NULL

ตัวอย่าง query ที่เขียนเสร็จแล้ว เช่น

SELECT
    row_number,
    TRIM(BOTH '"' FROM JSON_EXTRACT(datas.c_books,
                CONCAT('$[', row_number, '].title'))) AS title,
    TRIM(BOTH '"' FROM JSON_EXTRACT(datas.c_books,
                CONCAT('$[', row_number, '].author'))) AS author,
    TRIM(BOTH '"' FROM JSON_EXTRACT(datas.c_books,
                CONCAT('$[', row_number, '].publisher'))) AS publisher
FROM
    app_fd_books AS datas,
    (SELECT
        row_number AS row_number
    FROM
        integers) AS length
WHERE
    datas.id = 'b04469bd-ac1430f1-12bd2000-a4f7da3a'
        AND (SELECT
            TRIM(BOTH '"' FROM JSON_EXTRACT(datas.c_books,
                            CONCAT('$[', row_number, '].title')))
        FROM
            app_fd_books
        WHERE
            id = 'b04469bd-ac1430f1-12bd2000-a4f7da3a') IS NOT NULL
ORDER BY row_number;

แต่เมื่อใช้ไปนานๆ เจอวิธีที่เขียนได้ง่ายกว่าเดิมคือใช้ JSON_LENGTH ก็จะได้รูปแบบ

SELECT
    row_number,
    TRIM(BOTH '"' FROM JSON_EXTRACT(datas.c_books,
                CONCAT('$[', row_number, '].title'))) AS title,
    TRIM(BOTH '"' FROM JSON_EXTRACT(datas.c_books,
                CONCAT('$[', row_number, '].author'))) AS author,
    TRIM(BOTH '"' FROM JSON_EXTRACT(datas.c_books,
                CONCAT('$[', row_number, '].publisher'))) AS publisher
FROM
    integers AS i,
    app_fd_books AS datas
WHERE
    datas.id = 'b04469bd-ac1430f1-12bd2000-a4f7da3a'
        AND i.row_number < JSON_LENGTH(datas.c_books)
ORDER BY row_number;

มันง่าย และสั้นกว่าเยอะเลยพิชญ์