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