50강 SQL 초석 다지기 : PostgreSQL JSON Data Type

PostgreSQL강의 PostgreSQL강좌 SQL무료강의 SQL무료강좌 SQL기초강의  SQL기초강좌

50강 SQL 초석 다지기 : PostgreSQL JSON Data Type   

PostgreSQL

SQL 초석 다지기 50강 시작합니다.

본 강의는 PostgreSQL Tutorial을 참조합니다.  https://www.postgresqltutorial.com/

이번 시간에는

▶ PostgreSQL JSON Data Type

에 대해서 알아보는 시간입니다.

 

요약: 이번 강의에서는 PostgreSQL JSON 데이터 유형과 JSON 데이터를 처리하는 데 유용한 연산자 및 함수를 사용하는 방법에 대해 배울 것입니다.

 

JSON : JavaScript Object Notation 속성-값 쌍(attribute–value pairs), 배열 자료형(array data types) 또는 기타 모든 시리얼화 가능한 값(serializable value) 또는 "키-값 쌍"으로 이루어진 데이터 오브젝트를 전달하기 위해 인간이 읽을 수 있는 텍스트를 사용하는 개방형 표준 포맷이다. 비동기 브라우저/서버 통신 (AJAX)을 위해, 넓게는 XML(AJAX가 사용)을 대체하는 주요 데이터 포맷이다.

---

Introduction to PostgreSQL JSON data type   

JSON은 자바스크립트 객체 표기법의 약자이다. JSON은 키-값 쌍으로 구성된 개방형 표준 형식입니다.

JSON의 주요 용도는 서버와 웹 애플리케이션 간에 데이터를 전송하는 것이다. 다른 포맷과 달리 JSON은 사람이 읽을 수 있는 텍스트이다.

PostgreSQL은 버전 9.2 이후 네이티브 JSON 데이터 유형을 지원합니다. JSON 데이터를 조작하기 위한 많은 기능과 연산자를 제공한다.

이제 JSON 데이터 유형으로 연습할 수 있는 새로운 테이블을 만드는 것으로 시작하겠습니다.

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

 

주문 테이블은 두 개의 열로 구성됩니다.

● ID 열은 주문을 식별하는 기본 키 열입니다.
● INFO 열에는 JSON 형태로 데이터가 저장됩니다.

 

Insert JSON data   

데이터를 JSON 열에 삽입하려면 데이터가 유효한 JSON 형식인지 확인해야 합니다.  다음 INSERT 문은 주문 테이블에 새 행을 삽입합니다.

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

 

그것은 John Doe가 맥주 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}}');

 

 

Querying JSON data   

JSON 데이터를 쿼리하려면 다른 기본 데이터 유형을 쿼리하는 것과 유사한 SELECT 문을 사용합니다.

 

SELECT info FROM orders; 

PostgreSQL은 결과 집합을 JSON 형식으로 반환합니다.

PostgreSQL은 JSON 데이터를 쿼리하는 데 도움이 되는 두 가지 기본 연산자 -> 및 ->>를 제공합니다.

● 연산자 ->는 키별로 JSON 개체 필드를 반환합니다.
● 연산자 ->>는 JSON 객체 필드를 텍스트로 반환합니다.

 

다음 쿼리는 연산자 ->를 사용하여 모든 고객을 JSON 형식으로 가져옵니다.

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

 

그리고 다음 쿼리는 연산자 ->>를 사용하여 모든 고객을 텍스트 형태로 가져옵니다.

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

 

-> 연산자가 JSON 개체를 반환하므로 연산자 ->>와 연결하여 특정 노드를 검색할 수 있습니다. 예를 들어, 다음 문은 판매된 모든 제품을 반환합니다.

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

첫 번째 정보 -> 'items'은 items을 JSON 개체로 반환합니다. 그러면 info->'items'->>'product'는 모든 제품을 텍스트로 반환합니다.

 

---

Use JSON operator in WHERE clause   

전체 데이터에서 WHERE절을 이용하여 필요한 데이터를 찾아 봅시다.

 

WHERE 절의 JSON 연산자를 사용하여 반환되는 행을 필터링할 수 있습니다. 예를 들어, 누가 기저귀를 샀는지 확인하려면 다음 쿼리를 사용합니다.

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 ;

CAST를 사용하여 Qty 필드를 INTER 유형으로 변환하고 2와 비교했습니다.

CAST 연산자 cast 연산자는 변수의 형태를 임시적으로 변환하는 함수입니다. 형변환 함수

---

Apply aggregate functions to JSON data   

우리는 JSON 데이터에 MIN, MAX, AVERY, SUM 등의 집계 함수를 적용할 수 있습니다. 예를 들어, 다음 문구는 최소 수량, 최대 수량, 평균 수량 및 판매된 제품의 총 수량을 반환합니다.

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; 

---

PostgreSQL JSON functions   

PostgreSQL은 JSON 데이터를 처리하는 데 도움이 되는 몇 가지 기능을 제공합니다.

 

json_each function   

json_each() 함수를 사용하면 가장 바깥쪽의 JSON 개체를 키-값 쌍 집합으로 확장할 수 있습니다. 다음 설명을 참조하십시오.

SELECT json_each (info)
FROM orders; 

키-값 쌍 집합을 텍스트로 가져오려면 대신 json_each_text() 함수를 사용합니다.

SELECT json_each_text (info)
FROM orders;  

 

json_object_keys function   

가장 바깥쪽에 있는 JSON 개체의 키 집합을 가져오려면 json_object_keys() 함수를 사용합니다. 다음 쿼리는 정보 열에 있는 중첩된 항목 개체의 모든 키를 반환합니다.

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

 

json_typeof function    

json_typeof() 함수는 가장 바깥쪽에 있는 JSON 값의 유형을 문자열로 반환합니다. number, boolean, null, object, array  및 string일 수 있습니다.

다음 쿼리는 항목의 데이터 유형을 반환합니다.

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

 

다음 쿼리는 중첩된 항목 JSON 개체의 qty 필드의 데이터 형식을 반환합니다.

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

---

지금까지 JSON DATA TYPE과 몇가지 FUNCTION에 대해 공부했습니다.

 

PostgreSQL JSON 기능에 대해서 더 깊이 파고들고 싶다면,  

https://www.postgresql.org/docs/

에서 메뉴얼을 다운 받아서 보기를 추천드린다.

 

수고하셨습니다.

---

Do it! SQL을 찾아 주셔서 감사합니다. 공감 ♥ , 댓글이 큰 힘이 됩니다.