[PostgresSQL] PostgresSQL로 JSON 만들기

※ 일본의 한 블로그 글을 번역한 포스트입니다. 오역 및 의역, 직역이 있을 수 있으며 틀린 내용은 지적해주시면 감사하겠습니다.

 

 

이번 포스트에서 설명할 함수

---

이번 포스트에서 설명할 함수는 ROW_TO_JSON과 JSON_AGG두 가지이다. 각각의 함수로 출력되는 JSON형식은 다음과 같다.

 

ROW_TO_JSON

{"id":1,"name":"taro"}

 Object같은 JSON을 반환하는 함수

 

JSON_AGG

[{"id":1,"name":"taro"},{"id":2,"name":"jiro"}]

Array형식의 JSON을 반환하는 함수

 

 

ROW_TO_JSON

---

ROW란?

 ROW_TO_JSON의 ROW는 행이라고 할 수 있지만, 실체는 튜플이라고 생각하는 것이 좋다. 메뉴얼에는 "복합 값"이라는 이름으로 등장한다.

 아래와 같은 SQL로 생성할 수 있다.

SELECT ROW(1,2,3,4)

 SQL안에 두 개 이상의 필드가 있는 경우에는 ROW 키워드는 실제로 생략할 수 있다. 필드가 1개뿐이이면, 유일한 값이 되어 버린다.

SELECT (1,2,3,4); --> (1,2,3,4)
SELECT (1); --> 1
SELECT ROW(1); --> (1)

 

ROW_TO_JSON의 간단한 예

 이 예와 같이 무명의 튜플을 ROW_TO_JSON에 전달하면 각각의 값에 대해서 f1, f2, f3, f4와 같은 연속된 번호의 이름이 자동적으로 부여된 Object의 JSON이 반환된다.

SELECT ROW_TO_JSON(ROW(1,2,3,4));
--           row_to_json
-- -------------------------------
--  {"f1":1,"f2":2,"f3":3,"f4":4}
-- (1 row)

 

형의 부여

형의 정의

PostgreSQL에서는 복합형이라는 이름이 붙은 튜플을 표시하는 형태를 정의할 수 있다.

CREATE TYPE hoge_numbers as (
            n1 integer
          , n2 integer
          , n3 integer
          , n4 integer);

 또한 테이블을 생성할 때에는 테이블의 행 형태를 표현하기 위해 테이블명과 동일한 이름의 복합형도 자동적으로 생성된다.

CREATE TABLE hoge_numbers(
             n1 integer
           , n2 integer
           , n3 integer
           , n4 integer);

특정 형으로의 캐스트

 형의 조합이 맞으면 (1,2,3,4)::hoge_numbers이라는 느낌으로 무명 튜플을 이름 붙은 형태로 캐스트할 수 있다.

 이름이 붙은 형태로 캐스트된 튜플을 ROW_TO_JSON의 인수로서 전달할 수 있으므로, 의도한대로 각 항목에 이름이 붙은 Object의 JSON이 반환된다.

 SELECT ROW_TO_JSON((1,2,3,4)::hoge_numbers);
--           row_to_json
-- -------------------------------
--  {"n1":1,"n2":2,"n3":3,"n4":4}
-- (1 row)

 

 

 

JSON_AGG

---

AGG이란?

 AGG는 aggregate의 약어로, 요컨대 SQL에서의 SUM()이라던가 COUNT()의 동료라고 생각해도 무방하다.  그 자체로 사용가능하지만, SELECT할 때에 일부의 값만을 적용하고 싶은 경우는 GROUP BY와 함께 사용해야 한다. 

 

예

 이번 예에서는 WITH로 만든 인라인의 "user" 테이블의 행을 그대로 JSON_AGG()인수에 넣는 것으로, 각 행에 ROW_TO_JSON()을 적용한 결과를 Array로 aggregate한 것과 같은 JSON이 반환된다.

 WITH "user"("id", "name") AS (
VALUES (1, 'taro')
     , (2, 'jiro'))

SELECT JSON_AGG("user")
  FROM "user";

--            json_agg
-- ---------------------------
--  [{"id":1,"name":"taro"}, +
--   {"id":2,"name":"jiro"}]
-- (1 row)

 

 

응용

---

 ROW_TO_JSON()과 JSON_AGG()를 합쳐서, 복잡한 형태를 가진 오브젝트를 SQL 하나로 획득하는 것이 가능하거나 한다.

    WITH "user"("id", "name") AS (
  VALUES (1, 'taro')
       , (2, 'jiro')
       , (3, 'sabu')
       , (4, 'siro'))

       , "friendship"("from", "to") AS (
  VALUES (1, 2), (1, 3), (1, 4)
       , (2, 3)
       , (3, 1), (3, 2), (3, 4))

       , "t" AS (
  SELECT "me"."id"
       , "me"."name"
       , ARRAY_AGG("friend") AS friends
    FROM "user" AS "me"
    JOIN "friendship" ON "me"."id" = "from"
    JOIN "user" AS "friend" ON "friend"."id" = "to"
GROUP BY 1, 2)

  SELECT JSON_AGG("t") AS "data"
    FROM "t"
;
-- data
-- --------------------------------------------------------------
-- [{"id":1,"name":"taro","friends":[{"id":2,"name":"jiro"},   +
-- {"id":3,"name":"sabu"},                                    +
-- {"id":4,"name":"siro"}]},                                  +
-- {"id":2,"name":"jiro","friends":[{"id":3,"name":"sabu"}]}, +
-- {"id":3,"name":"sabu","friends":[{"id":1,"name":"taro"},   +
-- {"id":2,"name":"jiro"},                                    +
-- {"id":4,"name":"siro"}]}]
-- (1 row)

[
  {
    "id":1,
    "name":"taro",
    "friends":[
      {"id":2,"name":"jiro"},
      {"id":3,"name":"sabu"},
      {"id":4,"name":"siro"}
    ]
  },
  {
    "id":2,
    "name":"jiro",
    "friends":[
      {"id":3,"name":"sabu"}
    ]
  },
  {
    "id":3,
    "name":"sabu",
    "friends":[
      {"id":1,"name":"taro"},
      {"id":2,"name":"jiro"},
      {"id":4,"name":"siro"}
    ]
  }
]