[PostgreSQL] JSON 타입 handling

JSON 데이터 타입 선언하기

CREATE TABLE orders (
	ID serial NOT NULL PRIMARY KEY,
    info json NOT NULL
);

JSON 데이터 삽입하기

INSERT INTO orders (info) VALUES
(
	'{ "customer": "John Doe", "items": {"product":"Beer", "qty": 6}}'
);

JSON 데이터용 연산자 제공

-> : 연산자는 키 형태로 JSON 객체를 반환합니다.
->> : 연산자는 텍스트 형태로 JSON 객체를 반환합니다.

다음의 쿼리는 "->" 연산자를 이용하여 info 컬럼의 customer 키에 해당하는 데이터들을 가져옵니다. (json 타입으로 반환)

SELECT info -> 'customer' AS customer FROM orders;

-- 결과
"John Doe"

다음의 쿼리는 "->>" 연산자를 이용해 info 컬럼의 customer 키에 해당하는 데이터를 가져옵니다. (text 타입으로 반환)

SELECT info ->> 'customer' AS customer FROM orders;

-- 결과
John Doe

WHERE에 JSON 연산자 사용

SELECT info ->> 'customer' AS customer FROM orders
WEHRE info -> 'items' ->> 'product' = 'Diaper';

-- 결과
Lily Bush

한번에 2개의 물건을 산 사람을 찾기 위해 다음과 같이 쿼리문을 작성할 수도 있습니다.
WHERE 절에서 텍스트 타입으로 반환한 qty '2'값을 integer로 변환하여 숫자 2와 비교해야 하는 것을 주의

SELECT
	info ->> 'customer' AS customer,
    info -> 'items' ->> 'product' AS product
FROM orders
WHERE CAST (info -> 'items' ->> 'qty' AS INTEGER ) = 2

JSON 데이터에 집계함수 사용

Min, max, average, sum과 같은 집계함수를 json 타입의 데이터에 사용해보겠습니다.
최소 qty, 최대qty, 평균qty, 총 qty를 반환

SELECT
	MIN (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
    MAX (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
    SUM (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
    AVG (CAST (info -> 'items' ->> 'qty' AS INTEGER))
FROM orders

json_each() 함수

• json_each() 함수는 가장 바깥쪽의 json 정보를 키/값 셋으로 반환해줍니다.
• 만일 키/값 셋을 text 데이터 타입으로 반환받고 싶다면 json_each_text() 함수를 사용할 수 있습니다.

SELECT json_each (info) FROM orders;

json_object_keys 함수

• 가장 바깥쪽 json 데이터의 키셋을 얻으려면 json_object_keys() 함수를 사용할 수 있습니다. 아래의 쿼리문은 info 컬럼의 items 라는 json 데이터의 키셋을 얻어옵니다.

SELECT json_object_keys (info->'itmes') FROM orders;

json_typeof 함수 (리턴타입: varchar)

• json_typeof() 함수는 json 데이터의 가장 바깥쪽 값들의 타입을 반환해줍니다.
• number, boolean, null, object, array, string 같은 데이터가 될 수 있습니다.

아래의 쿼리문은 items라는 프로퍼티의 value에 해당하는 데이터 타입을 반환해줍니다.

SELECT json_typeof (info->'items') FROM orders;

아래의 쿼리문은 중첩된 items json타입의 키가 qty에 해당하는 값의 타입을 반환합니다.

SELECT json_typeof (info->'items'->'qty') FROM orders;

테스트 데이터 만들기

CREATE TABLE users (
    name varchar(32),
    age int
)

INSERT INTO users 
('홍길동', 20)
, ('영이', 22)
, ('철이', 30);

문자열 합치기

SELECT name||' / ' ||age AS name_age FROM users;

-- Result
name_age
------------------
홍길동 / 20
영이 / 22
철이 / 30

테이블 전체를 JSON 만들기

SELECT array_to_json(array_agg(users)) FROM users;

-- Result: array_to_json
----------------
[{"name":"홍길동", "age":20"},{"name":"영이","age":22},{"name":"철이", "age":30}]

다중 행을 JSON 만들기

SELECT array_to_json(array(
	SELECT row_to_json(tmp) FROM(
    	SELECT name FROM users GROUP BY name) tmp));
        
-- Result: array_to_json
[{"name":"홍길동"},{"name":"영이"},{"name":"철이"}]

전체 열을 행 단위로 JSON 만들기

SELECT row_to_json(u) FROM USERS;

-- Result: row_to_json
{"name":"홍길동", "age":20}
{"name":"영이", "age":22}
{"name":"철이", "age":30}

특정 열만 행 단위로 JSON 만들기

SELECT row_to_json(row(name, age)) FROM users;

-- Result: row_to_json
{"f1":"홍길동", "f2":20}
{"f1":"영이", "f2":22}
{"f1":"철이", "f2":30}

SELECT row_to_json(tmp)
	FROM (
    	SELECT name, age FROM users
        )
    ) tmp;

-- Result: row_to_json
{"name":"홍길동", "age":20}
{"name":"영이", "age":22}
{"name":"철이", "age":30}

특정 열만 열 단위로 JSON 만들기

SELECT row_to_json(tmp)
FROM(
	SELECT 
    	array_agg(u.name) AS name,
        array_agg(u.age) AS age
    FROM users u
) tmp;

-- Result: row_to_json
-------------------------
 {"name":["홍길동","영이","철이"],"age":[20,22,30]}

특정 열을 기준, 지정한 구분자로 문자열 만들기

SELECT name, string_agg(age::text, ',') FROM users GROUP BY name;

-- Result
  name  | string_agg
--------+------------
 홍길동 | 20
 영이   | 22
 철이   | 30

-- Query 2 (PostgreSQL 8.4)
SELECT name, array_to_string(array_agg(age), ',') FROM users GROUP BY name;

-- Result
  name  | array_to_string
--------+-----------------
 홍길동 | 20
 영이   | 22
 철이   | 30