Mysql การดึงข้อมูลจากรูปแบบ json ที่เก็บเป็น array ไว้ จะเขียน query ซับซ้อนกว่าปกตินิดหนึ่งเพราะว่าต้องมีการดึงข้อมูลโดยทราบจำนวนแถว เช่น จากข้อมูล
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 | 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 ) เอาไว้ให้ใช้ง่าย ๆ เช่น
integers
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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) ก็จะเขียนประมาณ
exit condition
1 2 3 4 5 6 7 | 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 ที่เขียนเสร็จแล้ว เช่น
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 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 publisherFROM app_fd_books AS datas, (SELECT row_number AS row_number FROM integers) AS lengthWHERE 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 NULLORDER BY row_number; |
แต่เมื่อใช้ไปนานๆ เจอวิธีที่เขียนได้ง่ายกว่าเดิมคือใช้ JSON_LENGTH ก็จะได้รูปแบบ
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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 publisherFROM integers AS i, app_fd_books AS datasWHERE datas.id = 'b04469bd-ac1430f1-12bd2000-a4f7da3a' AND i.row_number < JSON_LENGTH(datas.c_books)ORDER BY row_number; |
มันง่าย และสั้นกว่าเยอะเลยพิชญ์
About the author