[SQL] 반환 JSON의 배열 SQL에서 객체로 (포스트 그레스)

반환 JSON의 배열 SQL에서 객체로 (포스트 그레스)

나는 다음과 같은 mytable 테이블이 있습니다

 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

나는 value_two에 의해 그룹화 된 개체 {value_three, value_four}의 배열을 조회 할. value_two는 결과 자체에 존재해야한다. 결과는 다음과 같아야합니다 :

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

이 json_agg () 또는 array_agg를 사용하는지 여부는 중요하지 않습니다 ().

그러나 내가 할 수있는 최선입니다 :

with MyCTE as ( select value_two, value_three, value_four from MyTable ) 
select value_two, json_agg(row_to_json(MyCTE)) value_four 
from MyCTE 
group by value_two;

어떤 반환 :

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

내가 제거하고자하는 목적에 추가 value_two 키. 나는 어떤 SQL (포스트 그레스) 쿼리를 사용해야합니까?

해결법

1. ==============================

   1.

   SELECT value_two, json_agg(json_build_object('value_three', value_three
                                              , 'value_four' , value_four)) AS value_four
   FROM   mytable 
   GROUP  BY value_two;

   수동 :

   행 식 row_to_json는 () 트릭을 할 것입니다 :

   SELECT value_two
        , json_agg(row_to_json((value_three, value_four))) AS value_four
   FROM   mytable
   GROUP  BY value_two;
   

   하지만 당신은 원래의 열 이름을 잃게됩니다. 등록 된 행 유형에 대한 캐스트는 것을 방지 할 수 있습니다. (임시 테이블의 행의 형태도 임시 쿼리에 대한 역할을한다.)

   CREATE TYPE foo AS (value_three text, value_four text);  -- once in the same session
   

   SELECT value_two
        , json_agg(row_to_json((value_three, value_four)::foo)) AS value_four
   FROM   mytable
   GROUP  BY value_two;

   아니면 부속 대신 ROW 식을 사용합니다. 더 자세한하지만, 타입 캐스트없이 :

   SELECT value_two
        , json_agg(row_to_json((SELECT t FROM (SELECT value_three, value_four) t))) AS value_four
   FROM   mytable
   GROUP  BY value_two;

   크레이그의 관련 대답 더 많은 설명 :

   DB <> 바이올린 여기 올드 SQL 바이올린.

from https://stackoverflow.com/questions/26486784/return-as-array-of-json-objects-in-sql-postgres by cc-by-sa and MIT license