데이터베이스 테이블 내 하나의 컬럼에 JSON 데이터를 저장하는 경우가 있습니다. 보통 외부에서 제공된 데이터를 별도의 처리 없이 그대로 저장할 때 JSON 타입으로 저장하게 됩니다.
PostgreSQL은 JSON 데이터를 저장하기 위한 2가지 타입인 json과 jsonb 타입을 제공합니다.
1. json vs jsonb 타입
그렇다면 json과 jsonb 타입의 차이점은 무엇일까요?
json 타입은 입력된 텍스트 원본을 저장하고, jsonb 타입은 decopmose된 바이너리 형식으로 저장한다는 차이가 있습니다.
json 타입은 입력된 텍스트에 대한 정확한 복사본을 저장하기 때문에, 처리할 때마다 매번 파싱을 다시 해야만 합니다.
jsonb 타입에 비해 처리 속도가 느리나, 원본 그대로를 저장할 수 있다는 장점이 있습니다.
jsonb 타입은 정제된 데이터를 저장합니다. 데이터를 저장할 땐 추가적인 변환이 필요해 속도가 약간 느리지만, 이후 추가적인 파싱이 필요하지 않으므로 처리 속도가 json 타입에 비해 빠릅니다. 또한, jsonb 타입은 인덱싱을 지원한다는 아주 강력한 이점도 갖고 있습니다.
json 타입은 입력된 텍스트의 정확한 복사본을 저장하기에 토큰 사이의 의미없는 공백도 모두 저장하며, JSON 객체 내 키 순서도 보장합니다. 또한, 중복된 키가 있는 경우에도 모든 key가 중복되어 저장됩니다.
반면 jsonb 타입은 공백을 보존하지 않고, 객체 키의 순서를 보장하지 않으며 중복 객체 key 또한 저장하지 않습니다. 만약 중복 key 값이 입력되면 마지막 key의 값만을 저장합니다.
>> SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
{"bar": "baz", "balance": 7.77, "active":false}>> SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
{"bar": "baz", "active": false, "balance": 7.77}
일반적으로 대부분의 애플리케이션은 기존 객체 키에 대한 순서 보장과 같은 요구사항이 없는 한 json 데이터를 jsonb 타입으로 저장합니다.
2. json 연산자
1) ->, ->>
json 배열에서 인덱스 또는 key에 해당하는 요소를 추출하고 싶을 때가 있습니다. 이때 int 형이 주어지면 배열 내 인덱스로, text 형이 주어진다면 해당 key에 대응하는 요소를 추출합니다.
>> SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb->2;
{"c": "baz"}
>> SELECT '{"a": {"b":"foo"}}'::jsonb->'a'
{"b": "foo"}>> SELECT '[1,2,3]'::jsonb->>2
3
>> SELECT '{"a":1,"b":2}'::json->>'b'
2
-> 연산자는 결과를 json 형태로 추출하며, ->> 연산자는 결과를 text 형으로 추출합니다.
>> SELECT pg_typeof('[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb->2);
jsonb
>> SELECT pg_typeof('[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb->>2);
text
2) #>, #>>
PostgreSQL에서 jsonb 컬럼을 조회할 때 중첩된 필드의 특정 조건만을 검색하고 싶을 때가 있습니다.
이때 #>, #>> 연산자를 사용하면 원하는 데이터를 추출할 수 있습니다. 검색 조건으로 활용하는 필드는 콤마로 구분합니다.
#> 연산자는 결과를 json 형태로 추출하며, #>> 연산자는 결과를 text 형으로 추출합니다.
>> SELECT '{"a": {"b":{"c": "foo"}}}'::jsonb#>'{a,b}';
{"c": "foo"}
위 연산자들은 json형으로 저장된 컬럼에 원하는 조건에 해당하는 데이터를 추출하고 싶을 때 사용할 수 있습니다.
SELECT *
FROM member
WHERE memberInfo #>> '{user,hostname}' = 'yeongun'SELECT *
FROM member
WHERE memberInfo ->> 'user' = '{"hostname": "yeongun"}'3. jsonb 전용 연산자
jsonb 타입에서만 사용할 수 있는 연산자도 존재합니다.
1) @>, <@
@>, <@ 연산자는 주어진 jsonb 값이 있는지 여부를 반환합니다. 결과는 true 또는 false 중 하나입니다.
>> SELECT '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb;
true
>> SELECT '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb;
true
2) ?, ?|, ?&
?, ?|, ?& 연산자는 jsonb 데이터 내 key가 존재하는지를 판단합니다. 결과는 true 또는 false 중 하나입니다.
? 연산자는 jsonb 데이터 내 해당 key가 존재하는지 여부를 반환합니다.
>> SELECT '{"a":1, "b":2}'::jsonb ? 'b';
true
>> SELECT '{"a":1, "b":2}'::jsonb ? 'c';
false
?|, ?& 연산자는 검색 조건으로 배열이 주어집니다.
?| 연산자는 주어진 배열 내 값들 중 jsonb 데이터의 key로 하나라도 존재하면 true를 반환합니다.
>> SELECT '{"a":1, "b":2, "c":3}'::jsonb ?| array['c', 'd'];
true
>> SELECT '{"a":1, "b":2, "c":3}'::jsonb ?| array['d', 'e'];
false
?& 연산자는 주어진 배열 내 값들이 모두 jsonb 데이터의 key로 존재할 때 true를 반환합니다.
>> SELECT '["a", "b"]'::jsonb ?& array['a', 'b'];
true
>> SELECT '["a", "b"]'::jsonb ?& array['a', 'c'];
false
3) ||
|| 연산자는 주어진 2개의 jsonb 데이터를 하나로 합칩니다.
>> SELECT '["a", "b"]'::jsonb || '["c", "d"]'::jsonb
["a", "b", "c", "d"]
4) -
- 연산자는 jsonb 데이터 내 주어진 key를 제거합니다.
>> SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'a';
{"b": 2, "c": 3}
text 형이 아닌 int 형이 주어지면 배열 내 특정 인덱스에 해당하는 요소를 제거합니다.
>> SELECT '["a", "b", "c"]'::jsonb - 1;
["a", "c"]
5) #-
#- 연산자는 주어진 경로에 해당하는 값을 제거합니다.
>> SELECT '["a", {"b":{"c": "foo"}}]'::jsonb #- '{1,b,c}';
["a", {"b": {}}]
마무리
이번 글에서는 PostgreSQL에서 제공하는 json, jsonb 타입의 차이와 json 연산자에 대해 알아보았습니다. 제가 이번 주제를 공부하게 된 계기는 실무에서 jsonb 컬럼 내 데이터를 where 조건으로 검색할 일이 생겼기 때문입니다. 당장 처리해야될 업무는 아니어서 다행이었지만, 만약 데이터가 꼬여 급하게 조회할 필요성이 생겼다면 많이 당황했을 것 같습니다.
이 글을 읽는 분들도 PostgreSQL의 json 연산자에 대해 미리 공부한다면, 추후 장애 대응 시에도 큰 도움이 될 것 같습니다.
출처
'Programming > 데이터베이스' 카테고리의 다른 글
| M:N 관계 (0) | 2021.07.21 |
|---|---|
| 1:M 재귀적 관계 (0) | 2021.07.20 |
| 1:M 관계 (0) | 2021.07.20 |
| 주 식별자 (Primary Key) 설계 (0) | 2021.07.20 |
| 관계형 데이터베이스(RDBMS), 주식별자와 후보식별자 (0) | 2021.07.17 |