Bagaimana untuk Menjana Objek JSON Bersarang daripada Data Hubungan MySQL Menggunakan JSON_OBJECT dan JSON_ARRAY?

Menjana Objek JSON Bersarang dengan Fungsi JSON Asli MySQL

MySQL versi 5.7.12 memperkenalkan fungsi JSON asli, membolehkan penciptaan dokumen JSON terus daripada data hubungan. Untuk mencapai matlamat ini, seseorang boleh memanfaatkan fungsi JSON_OBJECT() dan JSON_ARRAY().

Pertimbangkan contoh jadual yang disediakan:

CREATE TABLE `parent_table` (
   `id` int(11) NOT NULL,
   `desc` varchar(20) NOT NULL,
   PRIMARY KEY (`id`)
);
CREATE TABLE `child_table` (
   `id` int(11) NOT NULL,
   `parent_id` int(11) NOT NULL,
   `desc` varchar(20) NOT NULL,
   PRIMARY KEY (`id`,`parent_id`)
);

Matlamatnya adalah untuk menjana dokumen JSON dengan objek bersarang, menyerupai yang berikut:

[{
    "id" : 1,
    "desc" : "parent row 1",
    "child_objects" : [{
            "id" : 1,
            "parent_id" : 1,
            "desc" : "child row 1"
        }, {
            "id" : 2,
            "parent_id" : 1,
            "desc" : "child row 2"
        }
    ]
}]

Menyelesaikan Ralat

Percubaan sebelumnya telah menemui ralat "Subquery mengembalikan lebih daripada 1 baris" kerana objek JSON memerlukan pasangan nilai kunci mudah sebagai input. Subkueri yang mengembalikan berbilang baris perlu ditukar kepada rentetan tunggal atau tatasusunan JSON.

Penyelesaian

Untuk mengatasi ralat ini, fungsi GROUP_CONCAT() MySQL boleh digunakan. Pertanyaan berikut menyelesaikannya:

select json_object(
  'id',p.id 
 ,'desc',p.`desc`
 ,'child_objects',json_array(
                     (select GROUP_CONCAT(
                                 json_object('id',id,'parent_id',parent_id,'desc',`desc`)
                             )   
                      from child_table 
                      where parent_id = p.id))
                   )
 from parent_table p;

Untuk menghapuskan aksara melarikan diri dalam output, pertanyaan diubah suai untuk menggunakan fungsi CAST() dan CONCAT():

select json_object(
  'id',p.id 
 ,'desc',p.`desc`
 ,'child_objects',(select CAST(CONCAT('[',
                GROUP_CONCAT(
                  JSON_OBJECT(
                    'id',id,'parent_id',parent_id,'desc',`desc`)),
                ']')
         AS JSON) from child_table where parent_id = p.id)

 ) from parent_table p;

Pertanyaan yang diperhalusi ini menghasilkan output JSON yang dikehendaki:

'{\"id\": 1, 
\"desc\": \"parent row 1\", 
\"child_objects\": 
    [{\"id\": 1, 
    \"desc\": \"child row 1\", 
    \"parent_id\": 1
    }, 
    {\"id\": 2, 
    \"desc\": \"child row 2\", 
    \"parent_id\": 1
    }]  
}'

Atas ialah kandungan terperinci Bagaimana untuk Menjana Objek JSON Bersarang daripada Data Hubungan MySQL Menggunakan JSON_OBJECT dan JSON_ARRAY?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!