Bagaimana untuk Mengembalikan Pelbagai Lajur sebagai JSON Array Objek dalam PostgreSQL?

Menjana Tatasusunan JSON Objek daripada Berbilang Lajur PostgreSQL

Panduan ini menunjukkan cara untuk mendapatkan semula data daripada jadual PostgreSQL sebagai tatasusunan objek JSON, mengumpulkan mengikut lajur tertentu. Contoh menggunakan jadual MyTable sebagai rujukan:

id	value_two	value_three	value_four
1	a	A	AA
2	a	A2	AA2
3	b	A3	AA3
4	a	A4	AA4
5	b	A5	AA5

Objektifnya adalah untuk mencipta tatasusunan JSON di mana setiap objek mengandungi value_three dan value_four, dikumpulkan mengikut value_two. Format output yang dikehendaki ialah:

value_two	value_four
a	[{"value_three":"A","value_four":"AA"}, {"value_three":"A2","value_four":"AA2"}, {"value_three":"A4","value_four":"AA4"}]
b	[{"value_three":"A3","value_four":"AA3"}, {"value_three":"A5","value_four":"AA5"}]

Penyelesaian PostgreSQL (Versi Khusus)

Penyelesaian optimum bergantung pada versi PostgreSQL anda:

PostgreSQL 10 dan ke atas:

Memanfaatkan operator - untuk pengecualian kunci memudahkan pertanyaan:

SELECT val2, jsonb_agg(to_jsonb(t.*) - '{id, val2}'::text[]) AS js_34
FROM tbl t
GROUP BY val2;

Ini dengan cekap mengalih keluar lajur yang tidak perlu (id dan val2) sebelum pengagregatan.

PostgreSQL 9.4 dan ke atas:

jsonb_build_object() menyediakan pendekatan yang lebih terkawal:

SELECT val2, jsonb_agg(jsonb_build_object('val3', val3, 'val4', val4)) AS js_34
FROM tbl 
GROUP BY val2;

Ini secara eksplisit menentukan pasangan nilai kunci untuk setiap objek JSON.

PostgreSQL 9.3 dan ke atas:

Beberapa pilihan wujud, setiap satu dengan pertukaran:

1. to_jsonb((val3, val4)): Ini kehilangan nama lajur dalam JSON yang terhasil.

2. (val3, val4)::foo: Ini mengekalkan nama lajur dengan menghantar kepada jenis baris tersuai (foo). Memerlukan mencipta jenis CREATE TYPE foo AS (val3 text, val4 text); terlebih dahulu.

3. Pendekatan subkueri: Kaedah yang lebih bertele-tele menggunakan subkueri tetapi mengelakkan penghantaran jenis.

SELECT val2, jsonb_agg(to_jsonb((val3, val4))) AS js_34  -- Loses column names
FROM tbl
GROUP BY val2;

-- Requires: CREATE TYPE foo AS (val3 text, val4 text);
SELECT val2, jsonb_agg((val3, val4)::foo) AS js_34  -- Preserves column names
FROM tbl
GROUP BY val2;

SELECT val2, jsonb_agg(to_jsonb((SELECT t FROM (SELECT val3, val4) t))) AS js_34 -- Subquery approach
FROM tbl
GROUP BY val2;

Pilih kaedah yang paling sesuai untuk versi PostgreSQL anda dan tahap ringkas yang diingini. Untuk versi 10 dan ke atas, pengendali - menawarkan penyelesaian yang paling bersih dan cekap.

Atas ialah kandungan terperinci Bagaimana untuk Mengembalikan Pelbagai Lajur sebagai JSON Array Objek dalam PostgreSQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!