LabEx
로그인무료 가입

PostgreSQL JSON/JSONB 고급 작업

PostgreSQLBeginner
지금 연습하기

소개

이 랩에서는 PostgreSQL 의 JSONB 데이터 타입을 사용한 고급 연산을 탐색합니다. 관계형 데이터베이스 내에서 JSON 데이터를 효과적으로 저장, 관리 및 쿼리하는 방법을 배우게 됩니다.

이 랩에서는 JSONB 컬럼이 있는 테이블을 생성하고, 데이터를 삽입한 다음, 검색 성능 향상을 위해 GIN 인덱스를 적용하는 과정을 안내합니다. 또한 중첩된 JSON 구조를 쿼리하고, JSONB 문서 내의 특정 요소를 업데이트하며, JSON 데이터에 대한 집계 계산을 수행하는 연습을 하게 됩니다.

테이블 생성 및 JSONB 필드 인덱싱

이 단계에서는 JSONB 데이터 타입을 사용하여 제품 정보를 저장할 테이블을 생성하고, 이 데이터에 대한 쿼리를 최적화하기 위해 GIN 인덱스를 생성합니다. JSONB는 JSON 데이터를 분해된 이진 형식으로 저장하므로 입력은 약간 느리지만 처리 속도는 훨씬 빠릅니다. GIN(Generalized Inverted Index) 은 JSONB 컬럼의 값과 같은 복합 값을 인덱싱하는 데 이상적입니다.

먼저 터미널을 열고 psql 대화형 쉘을 사용하여 PostgreSQL 데이터베이스에 연결합니다.

sudo -u postgres psql

이제 postgres=#와 같이 보이는 PostgreSQL 프롬프트가 표시됩니다.

다음으로 id 컬럼과 JSONB 타입의 data 컬럼을 가진 products라는 테이블을 생성합니다.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    data JSONB
);

이제 products 테이블에 샘플 제품 데이터를 삽입합니다.

INSERT INTO products (data) VALUES
('{"name": "Laptop", "price": 1200, "tags": ["electronics", "computer"]}'),
('{"name": "Keyboard", "price": 75, "tags": ["electronics", "accessory"]}'),
('{"name": "Mouse", "price": 30, "tags": ["electronics", "accessory"]}'),
('{"name": "Monitor", "price": 300, "tags": ["electronics", "display"]}');

data 컬럼 내 검색 속도를 크게 높이려면 해당 컬럼에 GIN 인덱스를 생성합니다.

CREATE INDEX idx_products_data ON products USING GIN (data);

\di 명령을 사용하여 인덱스가 성공적으로 생성되었는지 확인할 수 있습니다. 이 명령은 모든 인덱스를 나열합니다.

\di

아래 출력과 유사하게 관계 목록에서 idx_products_data를 볼 수 있습니다.

                                List of relations
 Schema |        Name         | Type  |  Owner   |   Table   |    Size    | Description
--------+---------------------+-------+----------+-----------+------------+-------------
 public | idx_products_data   | index | postgres | products  | 16 kB      |
 public | products_pkey       | index | postgres | products  | 16 kB      |
(2 rows)

테이블 설정 및 JSONB 컬럼 인덱싱을 성공적으로 완료했습니다. 다음 단계를 위해 psql 쉘에 계속 머물러 있습니다.

중첩된 JSON 구조 쿼리

이 단계에서는 중첩된 구조를 포함하여 JSONB 컬럼 내의 데이터를 쿼리하는 방법을 배웁니다. PostgreSQL 은 이 목적을 위해 여러 연산자를 제공합니다.

먼저, 더 복잡하고 중첩된 데이터를 가진 제품을 products 테이블에 삽입해 보겠습니다.

INSERT INTO products (data) VALUES
('{"name": "Gaming PC", "price": 1500, "specs": {"cpu": "Intel i7", "ram": "32GB", "storage": "1TB SSD"}}'),
('{"name": "Office PC", "price": 800, "specs": {"cpu": "Intel i5", "ram": "16GB", "storage": "500GB SSD"}}');

최상위 레벨 키에 접근하려면 값을 텍스트로 반환하는 ->> 연산자를 사용할 수 있습니다. "Laptop"의 가격을 찾아보겠습니다.

SELECT data ->> 'price' FROM products WHERE data ->> 'name' = 'Laptop';

결과는 다음과 같습니다.

 price
-------
 1200
(1 row)

중첩된 JSON 객체 내의 키에 접근하려면 -> 및 ->> 연산자를 연결하여 사용할 수 있습니다. -> 연산자는 JSON 객체 필드를 가져오고, ->>는 텍스트로 가져옵니다. "Gaming PC"의 CPU 사양을 검색해 보겠습니다.

SELECT data -> 'specs' ->> 'cpu' FROM products WHERE data ->> 'name' = 'Gaming PC';

이 명령은 CPU 유형을 반환합니다.

   ?column?
--------------
 Intel i7
(1 row)

이러한 연산자를 WHERE 절에서 사용하여 중첩된 값을 기반으로 결과를 필터링할 수도 있습니다. 예를 들어, "Intel i5" CPU 를 가진 모든 제품을 찾아보겠습니다.

SELECT data ->> 'name' FROM products WHERE data -> 'specs' ->> 'cpu' = 'Intel i5';

이 쿼리는 일치하는 제품의 이름을 반환합니다.

   name
-----------
 Office PC
(1 row)

이제 JSONB 컬럼 내의 최상위 레벨 데이터와 중첩된 데이터 모두를 쿼리할 수 있습니다.

특정 JSONB 요소 업데이트

이 단계에서는 jsonb_set 함수를 사용하여 JSONB 컬럼 내의 특정 요소를 수정하는 방법을 배웁니다. 이는 전체 JSON 객체를 검색하여 애플리케이션에서 수정하고 다시 쓰는 것보다 효율적입니다.

jsonb_set 함수는 다음과 같은 구문을 가집니다: jsonb_set(target_jsonb, path_array, new_value_jsonb, create_if_missing).

"Laptop"의 가격을 1200 에서 1250 으로 업데이트해 보겠습니다. 가격으로 가는 경로는 '{price}'이며, 새 값은 JSONB로 캐스팅되어야 합니다.

UPDATE products
SET data = jsonb_set(data, '{price}', '1250'::jsonb)
WHERE data ->> 'name' = 'Laptop';

Laptop 의 데이터를 선택하여 업데이트를 확인합니다.

SELECT data FROM products WHERE data ->> 'name' = 'Laptop';

결과는 새 가격을 보여야 합니다.

                                   data
--------------------------------------------------------------------------
 {"name": "Laptop", "price": 1250, "tags": ["electronics", "computer"]}
(1 row)

중첩된 값도 업데이트할 수 있습니다. "Office PC"의 RAM 을 "32GB"로 업그레이드해 보겠습니다. 중첩된 ram 키로 가는 경로는 '{specs,ram}'입니다.

UPDATE products
SET data = jsonb_set(data, '{specs,ram}', '"32GB"'::jsonb)
WHERE data ->> 'name' = 'Office PC';

이제 이 중첩된 업데이트를 확인합니다.

SELECT data -> 'specs' FROM products WHERE data ->> 'name' = 'Office PC';

결과는 ram 값의 변경을 확인시켜 줄 것입니다.

                          ?column?
------------------------------------------------------------
 {"cpu": "Intel i5", "ram": "32GB", "storage": "500GB SSD"}
(1 row)

이제 JSONB 데이터에 대한 대상 업데이트를 수행하는 방법을 배웠습니다.

JSON 데이터 집계

이 마지막 단계에서는 JSONB 컬럼에서 추출한 데이터에 대한 집계 계산을 수행합니다. 이는 보고 및 분석에 유용합니다.

계산을 수행하려면 값을 추출하여 숫자 유형으로 캐스팅해야 하는 경우가 많습니다. 모든 제품의 평균 가격을 계산해 보겠습니다.

SELECT AVG((data ->> 'price')::numeric) FROM products;

이 쿼리는 price를 텍스트로 추출하고, numeric으로 캐스팅한 다음, 평균을 계산합니다. 결과는 단일 숫자입니다.

          avg
------------------------
 659.1666666666666667
(1 row)

GROUP BY 절과 함께 집계 함수를 사용할 수도 있습니다. 각 태그별 제품 수를 찾아보겠습니다. 이를 위해 먼저 jsonb_array_elements_text를 사용하여 tags 배열을 개별 행으로 펼쳐야 합니다.

SELECT tag, COUNT(*)
FROM products, jsonb_array_elements_text(data -> 'tags') AS tag
GROUP BY tag;

이 쿼리는 각 고유 태그에 대한 개수를 생성합니다.

     tag     | count
-------------+-------
 accessory   |     2
 electronics |     4
 computer    |     1
 display     |     1
(4 rows)

마지막으로 "electronics" 태그를 가진 모든 제품의 총 가치를 찾아보겠습니다.

SELECT SUM((data ->> 'price')::numeric)
FROM products
WHERE data -> 'tags' @> '"electronics"'::jsonb;

@> 연산자는 왼쪽 JSONB 값이 오른쪽 JSONB 값을 포함하는지 확인합니다. 여기서는 tags 배열이 "electronics" 요소를 포함하는지 확인합니다.

결과는 네 개의 전자 제품 가격의 합계입니다.

  sum
--------
 1655
(1 row)

이제 JSONB 데이터에 대한 집계 함수를 사용하는 방법을 배웠습니다. psql 셸을 종료하려면 다음을 입력하십시오.

\q

요약

이 실습에서는 PostgreSQL 에서 JSONB 데이터를 처리하는 몇 가지 고급 작업을 배웠습니다. JSONB 컬럼이 있는 테이블을 생성하는 것으로 시작했으며 쿼리 성능을 최적화하기 위해 GIN 인덱스를 사용하는 것의 중요성을 확인했습니다. 그런 다음 -> 및 ->> 연산자를 사용하여 최상위 및 중첩 JSONB 데이터 모두를 쿼리하는 연습을 했습니다. 또한 jsonb_set 함수로 JSONB 문서에 대한 대상 수정을 수행하는 방법과 AVG, COUNT, SUM과 같은 집계 함수를 사용하여 JSON 데이터에 대한 강력한 데이터 분석을 수행하는 방법을 배웠습니다.

관련 PostgreSQL 코스
PostgreSQL 초보자를 위한 안내

PostgreSQL 초보자를 위한 안내

databasepostgresql
고급 PostgreSQL 실습 랩

고급 PostgreSQL 실습 랩

databasepostgresql