Wybierz kolumny w pliku json_agg

Mam zapytanie takie jak:

SELECT a.id, a.name, json_agg(b.*) as "item"
  FROM a
  JOIN b ON b.item_id = a.id
 GROUP BY a.id, a.name;

Jak mogę wybrać kolumny, baby nie mieć b.item_idobiektu JSON?

Czytałem o ROW, ale zwraca obiekt JSON, taki jak:

{"f1": "Foo", "f2": "Bar"}

Musiałbym ponownie mapować obiekt JSON po jego pobraniu w celu dopasowania odpowiednich kluczy kolumny. Chciałbym tego uniknąć i zachować oryginalne nazwy kolumn.

Niestety w składni SQL nie ma przepisu mówiącego „wszystkie kolumny oprócz tej jednej kolumny” . Możesz osiągnąć swój cel, wypisując pozostałą listę kolumn w wyrażeniu typu wiersz :

SELECT a.id, a.name
     , json_agg((b.col1, b.col2, b.col3)) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;

To skrót od bardziej zrozumiałej formie: . ROW(b.col1, b.col2, b.col3)

Jednak nazwy kolumn nie są zachowywane w wyrażeniach typu wierszowego. W ten sposób otrzymujesz ogólne nazwy kluczy w obiekcie JSON. Widzę 3 opcje zachowania oryginalnych nazw kolumn:

1. Przesyłaj do zarejestrowanego typu

Rzuć na dobrze znany (zarejestrowany) typ wiersza. Typ jest rejestrowany dla każdej istniejącej tabeli lub widoku lub z wyraźną CREATE TYPEinstrukcją. Możesz użyć tabeli tymczasowej dla rozwiązania ad-hoc (obowiązuje przez czas trwania sesji):

CREATE TEMP TABLE x (col1 int, col2 text, col3 date);  -- use adequate data types!

SELECT a.id, a.name
     , json_agg((b.col1, b.col2, b.col3)::x) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;

2. Użyj podselekcji

Użyj podselekcji, aby zbudować tabelę pochodną i odwołać się do tabeli jako całości . To także przenosi nazwy kolumn. Jest bardziej szczegółowy, ale nie potrzebujesz zarejestrowanego typu:

SELECT a.id, a.name
     , json_agg((SELECT x FROM (SELECT b.col1, b.col2, b.col3) AS x)) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;

3. json_build_object()w Postgres 9.4 lub nowszym

SELECT a.id, a.name
     , json_agg(json_build_object('col1', b.col1, 'col2', b.col2, 'col3', b.col3)) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;

Związane z:

• Zwraca jako tablicę obiektów JSON w SQL (Postgres)

Podobnie w jsonbprzypadku odpowiednich funkcji jsonb_agg()i jsonb_build_object().

Dla PostgreSQL 9.5 lub nowszy również zobaczyć odpowiedź a_horse w nowym wariancie krótszy składni: Postgres dodał operator minus -zajsonb powiedzieć „wszystkie klawisze z wyjątkiem tego jednego klucza” .
Ponieważ Postgres 10 „oprócz kilku kluczy” jest implementowany z tym samym operatorem, który bierze text[]jako drugi operand - jak komentuje mlt.

Począwszy od wersji 9.6, możesz po prostu użyć -do usunięcia klucza z JSONB:

SELECT a.id, a.name, jsonb_agg(to_jsonb(b) - 'item_id') as "item"
FROM a
  JOIN b ON b.item_id = a.id
GROUP BY a.id, a.name;

to_jsonb(b)przekształci cały wiersz, a - 'item_id'następnie usunie klucz o nazwie, item_idktórej wynik zostanie następnie zagregowany.

Możesz to zrobić bez grupowania za pomocą podkwerend

SELECT 
  a.id, a.name, 
  ( 
    SELECT json_agg(item)
    FROM (
      SELECT b.c1 AS x, b.c2 AS y 
      FROM b WHERE b.item_id = a.id
    ) item
  ) AS items
FROM a;

zwroty

{
  id: 1,
  name: "thing one",
  items:[
    { x: "child1", y: "child1 col2"},
    { x: "child2", y: "child2 col2"}
  ]
}

Ten artykuł od Johna Attena jest naprawdę interesujący i zawiera więcej szczegółów

Przekonałem się, że najlepiej jest utworzyć JSON, a następnie go zagregować. na przykład

with base as (
select a, b, ('{"ecks":"' || to_json(x) || '","wai":"' || to_json(y) || '","zee":"' || to_json(z) || '"}"')::json c
) select (a, b, array_to_json(array_agg(c)) as c)

Uwaga: można to zrobić jako podzapytanie, jeśli nie lubisz CTE (lub masz problemy z wydajnością z powodu korzystania z niego).

Zauważ też, że jeśli będziesz często to robić, może być korzystne utworzenie funkcji do zawijania dla Ciebie par klucz-wartość, aby kod wyglądał na czystszy. Przekazałeś swoją funkcję (na przykład) 'ecks', 'x'i ona powróciłaby "ecks": "x".

Chociaż nadal nie ma sposobu, aby zrobić nic z zaznaczeniem wszystkich kolumn oprócz jednego bitu, ale możesz użyć, json_agg(to_json(b.col_1, b.col_2, b.col_3 ...))aby uzyskać tablicę jsonów jsonów w formacie {"col_1":"col_1 value", ...}.

Tak więc zapytanie wyglądałoby mniej więcej tak:

SELECT a.id, a.name, json_agg(to_json(b.col_1,b.col_2,b.col_3...)) as item
  FROM a
  JOIN b ON b.item_id = a.id
GROUP BY a.id, a.name;

i zwraca wiersze jako:

id, name, item
8, the_name, [{"col_1":"value_1","col_2":"value_2","col_3":"value_3"...}, {"col_1":"value_1.2","col_2":"value_2.2","col_3":"value_3.2"...},...]
9, the_next_name, [{"col_1":"value_1.3","col_2":"value_2.3","col_3":"value_3.3"...},   {"col_1":"value_1.4","col_2":"value_2.4","col_3":"value_3.4"...},...]
...

(Jestem teraz na Postgres 9.5.3 i nie jestem w 100% pewien, kiedy ta obsługa została dodana).

Możesz użyć w json_build_objectten sposób

SELECT 
  a.id, 
  a.name,
  json_agg(json_build_object('col1', b.col1, 'col2', b.col2) AS item
FROM a
JOIN b ON b.item_id = a.id
GROUP BY a.id, a.name;