Bagaimana untuk Menjana Objek JSON Bersarang daripada Hubungan Satu-ke-Banyak dalam MySQL?

Menjana Objek JSON Bersarang dengan Fungsi JSON Asli MySQL

Artikel ini menunjukkan cara menggunakan fungsi JSON asli MySQL versi 5.7.12 untuk membina bersarang Dokumen JSON yang mewakili perhubungan satu-ke-banyak dalam perhubungan data.

Pernyataan Masalah

Diberikan dua jadual, parent_table dan child_table, yang mewakili hubungan ibu bapa-anak, matlamatnya adalah untuk menjana dokumen JSON dengan yang berikut struktur:

[
    {
        "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"
            }
        ]
    }
]

Penyelesaian

Pada mulanya, percubaan untuk mencipta objek JSON bersarang menggunakan subquery bersarang mudah mengakibatkan ralat kerana MySQL menjangkakan pasangan objek mudah dalam induk objek JSON. Untuk menyelesaikan masalah ini, kami menggunakan fungsi GROUP_CONCAT untuk menggabungkan berbilang hasil subkueri ke dalam satu rentetan, dan kemudian menukar hasil itu menjadi tatasusunan JSON.

Walau bagaimanapun, output JSON yang terhasil mengandungi aksara melarikan diri disebabkan oleh perlakuan subkueri sebagai seutas tali. Untuk membetulkannya, kami menggunakan fungsi CAST dan CONCAT untuk mencipta tatasusunan JSON dalam subkueri secara langsung, dan bukannya bergantung pada penggabungan dalam pertanyaan utama.

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 ini menghasilkan output yang diingini:

'{\"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 Hubungan Satu-ke-Banyak dalam MySQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!