PostgreSQL에서 json type

twoStones·2022년 4월 9일

PostgreSQL

PostgreSql에서는 컬럼 타입을 json 타입으로 지정하여 사용할 수 있다.
json, json[], jsonb, jsonb[] 4가지 타입이 있다.

타입 설명

json 타입

입력된 공백, 키 순서, 중복 등과 같은 모든 것들들 그대로 저장
조회 시 속도가 느림

jsonb 타입

키의 순서, 중복 제거, 공백 제거 등이 발생해 초기 입력된 값의 형태와 다르고 최적화된 값을 저장
조회 시 json 타입보다 빠름

저장 이후 조회가 많지 않다면 json 타입으로 저장
저장 이후 조회가 많다면 jsonb 타입으로 저장

데이터 조회 방법

-> 와 ->> 연산자를 사용하여 조회 가능

테스트 할 테이블 생성

create table test_table
(
	id serial primary key,
	jsonb_col jsonb
)
;

insert into test_table (jsonb_col) values ('{"age": "21", "name": "홍길동", "weapon": ["몽둥이", "칼"]}'); 
insert into test_table (jsonb_col) values ('{"age": "22", "name": "홍길금", "weapon": ["권총", "장총"]}'); 
insert into test_table (jsonb_col) values ('{"age": "15", "name": "홍길은", "weapon": ["새총"], "hasItem": "True"}');

-> 로 조회

필드 조회하기

-- hasItem 필드가 있는 데이터만 조회
select *
from test_table
where jsonb_col -> 'hasItem' is not null;

-- 해당 필드의 값을 뽑아낼 때
select jsonb_col -> 'hasItem'as hasItem 
from test_table
where jsonb_col -> 'hasItem' is not null;

-> 연산자로 조회한 값은 타입이 jsonb 타입이다

->> 로 조회

-- hasItem 필드가 있는 데이터만 조회
select *
from test_table
where jsonb_col ->> 'hasItem' is not null;

-- 해당 필드의 값을 뽑아낼 때
select jsonb_col ->> 'hasItem'as hasItem 
from test_table
where jsonb_col ->> 'hasItem' is not null;

->> 연산자로 조회한 값은 타입이 text 타입이다

json 필드 값 중에서 배열 타입인 필드의 값 조회하기

-- weapon 필드가 있고 그 필드의 첫 번째 값이 권총인 데이터 조회
select *
from test_table
where jsonb_col -> 'weapon' ->> 0 = '권총';

-- weapon 필드의 값을 jsonb 로 꺼냈기 때문에 조회할 수 없다
select *
from test_table
where jsonb_col -> 'weapon' -> 0 = '권총'