[PostgreSQL] 기본 데이터 타입 정리 - JSON

---

• Character types / why should we use char instead of varchar?
• Numeric types
• Boolean types
• Temporal types
• UUID for storing Universally Unique Identifiers
• Array for storing array strings, numbers, etc...
• JSON stores JSON data
• hstore stores key-value pair
• User defined data

---

JSON

JSON의 주 사용처는 서버와 웹 애플리케이션간 데이터 전송이다. 다른 형식과는 다르게 JSON은 사람이 읽을 수 있는 text 형식이다.

 

테이블 생성

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

 

데이터 삽입

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

INSERT INTO orders (info)
VALUES('{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'),
      ('{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}'),
      ('{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}');

 

쿼리

SELECT info FROM orders;

postgresql 에서는 -> 와 ->> 두가지 native operators를 제공한다.

 

-> : returns JSON object field by key.

->> : returns JSON object field by text.

 

-> 사용

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

 

->> 사용

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

 

응용

SELECT info -> 'items' ->> 'product' as product
FROM orders
ORDER BY product;

info -> 'items' 는 JSON objects를 반환한다. 그리고 info->'items'->>'product' 는 모든 products를 text로 반환한다.

 

where절에서의 응용

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

 

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

 

aggregate function 을 사용할 수 있다.

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() function

 

가장 바깥쪽 객체를 key-value 쌍으로 확장할 수 있게 해준다.

SELECT json_each (info)
FROM orders;

 

만약 text로 얻고싶다면 json_each_text() function 을 사용하면 된다.

 

json_object_keys function

 

set of key 를 얻고싶으면 json_object_keys() function을 사용하면된다.

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

 

json_typeof function

 

json_typeof() function은 outermost JSON value의 type를 string 형태로 반환한다.

 

올 수 있는 type - ( boolean, null, object, array, and string)

 

예)

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

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