TL; DR
SELECT json_agg(t) FROM t
dla tablicy obiektów JSON i
SELECT
json_build_object(
'a', json_agg(t.a),
'b', json_agg(t.b)
)
FROM t
dla obiektu JSON tablic.
Lista obiektów
W tej sekcji opisano sposób generowania tablicy obiektów JSON, przy czym każdy wiersz jest konwertowany na pojedynczy obiekt. Wynik wygląda następująco:
[{"a":1,"b":"value1"},{"a":2,"b":"value2"},{"a":3,"b":"value3"}]
9.3 i nowsze
json_aggFunkcja produkuje ten wynik po wyjęciu z pudełka. Automatycznie wymyśla, jak przekonwertować swoje dane wejściowe na JSON i agreguje je w tablicę.
SELECT json_agg(t) FROM t
Nie ma jsonb(wprowadzonej w 9.4) wersji json_agg. Możesz zagregować wiersze w tablicę, a następnie przekonwertować je:
SELECT to_jsonb(array_agg(t)) FROM t
lub połącz json_aggz obsadą:
SELECT json_agg(t)::jsonb FROM t
Moje testy sugerują, że najpierw agregowanie ich w tablicę jest trochę szybsze. Podejrzewam, że dzieje się tak, ponieważ rzutowanie musi przeanalizować cały wynik JSON.
9.2
9.2 nie ma funkcji json_agglub to_json, więc musisz użyć starszego array_to_json:
SELECT array_to_json(array_agg(t)) FROM t
Opcjonalnie możesz dołączyć row_to_jsonwywołanie do zapytania:
SELECT array_to_json(array_agg(row_to_json(t))) FROM t
To konwertuje każdy wiersz na obiekt JSON, agreguje obiekty JSON jako tablicę, a następnie konwertuje tablicę na tablicę JSON.
Nie byłem w stanie dostrzec żadnej znaczącej różnicy w wydajności między nimi.
Przedmiot list
W tej sekcji opisano sposób generowania obiektu JSON, w którym każdy klucz jest kolumną w tabeli, a każda wartość jest tablicą wartości kolumny. Oto wynik, który wygląda następująco:
{"a":[1,2,3], "b":["value1","value2","value3"]}
9.5 i nowsze
Możemy wykorzystać json_build_objectfunkcję:
SELECT
json_build_object(
'a', json_agg(t.a),
'b', json_agg(t.b)
)
FROM t
Możesz także zagregować kolumny, tworząc pojedynczy wiersz, a następnie przekonwertować go na obiekt:
SELECT to_json(r)
FROM (
SELECT
json_agg(t.a) AS a,
json_agg(t.b) AS b
FROM t
) r
Zwróć uwagę, że aliasowanie tablic jest absolutnie wymagane, aby zapewnić, że obiekt ma żądane nazwy.
Który z nich jest jaśniejszy, to kwestia opinii. Jeśli korzystasz z tej json_build_objectfunkcji, zdecydowanie zalecam umieszczenie jednej pary klucz / wartość w wierszu, aby poprawić czytelność.
Możesz również użyć array_aggzamiast json_agg, ale moje testy wskazują, że json_aggjest nieco szybszy.
Brak jsonbwersji json_build_objectfunkcji. Możesz agregować w jeden wiersz i konwertować:
SELECT to_jsonb(r)
FROM (
SELECT
array_agg(t.a) AS a,
array_agg(t.b) AS b
FROM t
) r
W przeciwieństwie do innych zapytań o tego rodzaju wynik, array_aggwydaje się być nieco szybszy podczas używania to_jsonb. Podejrzewam, że jest to spowodowane analizowaniem narzutów i sprawdzaniem poprawności wyniku JSON json_agg.
Lub możesz użyć wyraźnej obsady:
SELECT
json_build_object(
'a', json_agg(t.a),
'b', json_agg(t.b)
)::jsonb
FROM t
to_jsonbWersja pozwala uniknąć obsady i jest szybszy, według moich badań; ponownie podejrzewam, że jest to spowodowane narzutem związanym z analizowaniem i sprawdzaniem wyniku.
9.4 i 9.3
json_build_objectFunkcja była nowa do 9,5, więc trzeba kruszywo i skonwertować do obiektu w poprzednich wersjach:
SELECT to_json(r)
FROM (
SELECT
json_agg(t.a) AS a,
json_agg(t.b) AS b
FROM t
) r
lub
SELECT to_jsonb(r)
FROM (
SELECT
array_agg(t.a) AS a,
array_agg(t.b) AS b
FROM t
) r
w zależności od tego, czy chcesz, jsonczy jsonb.
(9.3 nie ma jsonb.)
9.2
W 9.2 nawet nie to_jsonistnieje. Musisz użyć row_to_json:
SELECT row_to_json(r)
FROM (
SELECT
array_agg(t.a) AS a,
array_agg(t.b) AS b
FROM t
) r
Dokumentacja
Znajdź dokumentację dotyczącą funkcji JSON w funkcjach JSON .
json_aggznajduje się na stronie funkcji agregujących .
Projekt
Jeśli wydajność jest ważna, upewnij się, że porównujesz swoje zapytania z własnym schematem i danymi, zamiast ufać moim testom.
To, czy jest to dobry projekt, czy nie, zależy tak naprawdę od konkretnej aplikacji. Jeśli chodzi o łatwość konserwacji, nie widzę żadnego szczególnego problemu. Upraszcza kod aplikacji i oznacza, że w tej części aplikacji jest mniej do utrzymania. Jeśli PG może dać dokładnie taki wynik, jakiego potrzebujesz po wyjęciu z pudełka, jedynym powodem, dla którego mogę wymyślić, aby go nie używać, byłyby względy wydajności. Nie odkrywaj na nowo koła i wszystkiego.
Zero
Funkcje agregujące zwykle zwracają, NULLgdy działają na zerowych wierszach. Jeśli jest taka możliwość, możesz użyć, COALESCEaby ich uniknąć. Kilka przykładów:
SELECT COALESCE(json_agg(t), '[]'::json) FROM t
Lub
SELECT to_jsonb(COALESCE(array_agg(t), ARRAY[]::t[])) FROM t
Kredyt do Hannes Landeholm za wskazanie tego