ABOUT ME

    -

    Today
    -
    Yesterday
    -
    Total
    -
    • [PostgreSQL] JSON type 사용하기
      DB/PostgreSQL 2020. 6. 7. 16:40

      json과 jsonb 타입

      PostgreSQL에서는 JSON 형식을 필드로 선언해 저장할 수 있습니다. 관련 타입은 json, json[], jsonb, jsonb[] 으로 4가지가 있습니다. 여기서 jsonb 라는 타입을 볼 수 있는데 json과 jsonb의 차이점은 입력된 값 그대로를 저장할 것인지 최적화된 값을 저장할 것인지 입니다.

      json 타입은 입력된 공백, 키 순서, 중복 등과 같은 모든 것을 그대로 저장합니다. 저장 이후 질의를 한다고 가정하면 json blob을 질의할 때마다 로드하고 구문을 분석하기 때문에 속도가 느린 단점이 있습니다.

      그에 반해 jsonb는 json blob의 줄임말로 입력된 값을 질의에 최적화된 형태로 저장합니다. 따라서 키의 순서, 중복 제거, 공백 제거 등이 발생해 초기 입력된 값의 형태와 다를 수 있습니다. 질의를 진행할 때, 이미 저장 단계에서 최적화가 완료되었으므로 재분석이 필요하지 않아 json보다 질의 속도가 빠른 장점을 가집니다.

      select '{"user_id":1,    "paying":true}'::json, '{"user_id":1, "paying":true}'::jsonb;



--             json                   |              jsonb
-- "{"user_id":1,    "paying":true}"	"{"paying": true, "user_id": 1}"

      저장 이후, 질의가 발생하지 않는다면 json 필드로, 잦은 질의가 발생한다고 예상이 되면 jsonb 필드로 저장하는 것이 좋습니다.

      데이터 조회

      JSON 타입의 컬럼에서 데이터를 조회할 때, -> 와 ->> 두 개의 연산자를 사용하여 가져올 수 있습니다.

      -> 로 키 조회하기

      -> 연산자는 json의 키 값 유무를 조회할 수 있습니다. 먼저 아래와 같은 json 데이터가 DB에 저장되어 있다고 가정합니다.

                       test_json
-----------------------------------------------
{"age": "21", "name": "LEE"}
{"age": "22", "name": "KIM"}
{"age": "15", "name": "PARK", "update": "True"}


      위와 같은 형태일 때, update 키가 존재하는 데이터만 추출하는 쿼리는 아래와 같습니다.

      select *
from test
where test_json -> 'update' is not null;


      만약 해당 키에 해당하는 값을 추출하고 싶으면 select 절에도 동일하게 -> 연산자를 사용하면 됩니다.

      select test_json -> 'update' as update
from test
where test_json -> 'update' is not null;

      ->> 로 값 조회하기

      ->> 연산자는 json의 키에 해당하는 값을 조회할 수 있고 텍스트 형식으로 반환이 됩니다. 위에서 DB에 저장되어 있다고 가정한 데이터에서 update 키의 값을 추출하는 쿼리는 아래와 같습니다.

      select test_json ->> 'update'
from test
where test_json ->> 'update' is not null


      쿼리의 결과를 보면 -> 연산자와 ->> 연산자의 결과가 동일한 것으로 보이지만 타입까지 비교를 하면 다르다는 것을 볼 수 있습니다.

      select test_json -> 'update' as update,
test_json ->> 'update as update2'
from test
where test_json -> 'update' is not null;




update          update2
jsonb            text
----------------------
true             true


      -> 연산자의 결과 타입은 jsonb이고 ->> 연산자의 결과 타입은 text입니다.

      json 필드 내, 리스트 타입의 값 추출하기

      먼저 아래와 같은 형식으로 json 데이터가 저장되어 있다고 가정합니다. 여기서 친구의 순서는 가장 친한 순서로 저장되어 있다고 생각합니다.

                       test_json
-----------------------------------------------
{"age": "21", "name": "LEE", "friend": ["KIM", "PARK"]}
{"age": "22", "name": "KIM", "friend": ["LEE", "PARK"]}
{"age": "15", "name": "PARK", "friend": ["KIM"], "update": "True"}


      아래는 위 데이터에서 1번째 입력된 친구가 KIM인 케이스를 추출하는 예시입니다.

      select *
from test
where test_json -> 'friend' ->> 0 = 'KIM';


      먼저 컬럼에서 -> 연산자를 사용해 friend의 키에 해당하는 값을 jsonb 형태로 추출하고 0번째에 해당하는 데이터를 ->> 연산자를 사용해 텍스트 타입으로 추출해 비교합니다.


      만약 리스트에 접근하는 방식은 ->> 연산자가 아닌 -> 연산자를 사용한다면 결과값은 jsonb 타입이므로 'KIM' 으로 비교하면 타입오류가 발생합니다. 오류가 안나도록 하려면 아래와 같이 큰따옴표를 붙여서 조회하거나 (저장된 형태 그대로) ? 연산자를 사용해 'KIM' 그대로를 조회할 수 있습니다.

      select *
from test
where test_json -> 'friend' -> 0 = '"KIM"';


또는


select *
from test
where test_json -> 'friend' -> 0 ? 'KIM';


      ? 연산자는 jsonb 타입에서 키가 존재하는지 비교하는 구문입니다. 따라서 ->> 로 결과를 추출한 다음 ? 연산자를 사용하면 type case error를 볼 수 있습니다. (->> 의 결과 타입은 text이고 ?는 jsonb에 해당하는 연산자이기 때문)

      JSON 타입에 인덱스 추가

      기본 인덱스 추가하기

      JSON 데이터 내의 값을 조회한다고 할 때, 데이터가 많을 경우 속도가 느릴 수 있습니다. 이를 방지하기 위해, JSON에서도 인덱스를 생성할 수 있습니다.

      CREATE INDEX 인덱스명 ON 테이블명((json컬럼명->>'key명'));

      성능 확인하기

      먼저 인덱스가 추가되어 있지 않는 상태에서 data 컬럼에서 finished 키의 값이 true인 개수를 추출하는 쿼리와 쿼리 플랜입니다.

      explain ANALYZE
SELECT count(*) 
FROM cards 
WHERE data->>'finished' = 'true';



Aggregate (cost=335.12..335.13 rows=1 width=0) (actual time=4.421..4.421 rows=1 loops=1) -> Seq Scan on cards (cost=0.00..335.00 rows=50 width=0) (actual time=0.016..3.961 rows=4938 loops=1)
    Filter: ((data ->> 'finished'::text) = 'true'::text)
    Rows Removed by Filter: 5062
Planning time: 0.071 ms
Execution time: 4.465 ms


      data 컬럼의 finished 키에 인덱스를 추가합니다.

      CREATE INDEX idx__cards_data_finished ON cards ((data->>'finished'));


      다음은 해당 json key에 인덱스를 추가한 후, 동일한 쿼리를 실행한 쿼리 플랜입니다.

      explain ANALYZE
SELECT count(*) 
FROM cards 
WHERE data->>'finished' = 'true';


Aggregate (cost=118.97..118.98 rows=1 width=0) (actual time=2.122..2.122 rows=1 loops=1) -> Bitmap Heap Scan on cards (cost=4.68..118.84 rows=50 width=0) (actual time=0.711..1.664 rows=4938 loops=1)
    Recheck Cond: ((data ->> 'finished'::text) = 'true'::text)
    Heap Blocks: exact=185
    -> Bitmap Index Scan on idxfinished (cost=0.00..4.66 rows=50 width=0) (actual time=0.671..0.671 rows=4938 loops=1)
        Index Cond: ((data ->> 'finished'::text) = 'true'::text)
Planning time: 0.084 ms
Execution time: 2.199 ms

      GIN 인덱스 추가하기

      아래는 ? 연산자를 사용해 질의를 하는 쿼리 플랜입니다.

      explain ANALYZE
SELECT count(*) FROM cards
WHERE
  data->'tags' ? 'Clean'
  AND data->'tags' ? 'Kitchen';




Aggregate (cost=385.00..385.01 rows=1 width=0) (actual time=6.673..6.673 rows=1 loops=1) -> Seq Scan on cards (cost=0.00..385.00 rows=1 width=0) (actual time=0.021..6.500 rows=1537 loops=1)
    Filter: (((data -> 'tags'::text) ? 'Clean'::text) AND ((data -> 'tags'::text) ? 'Kitchen'::text))
    Rows Removed by Filter: 8463
Planning time: 0.063 ms
Execution time: 6.710 ms
(6 rows)
Time: 7.314 ms


      PostgreSQL 9.4부터 JSONB 데이터 유형과 함께 GIN (Generalized Inverted Index) 인덱스가 추가되었습니다. 해당 인덱스를 사용하면 JSON 연산자인 @>, ?, ?&, ?| 등을 사용하여 데이터를 더 빠르게 질의할 수 있습니다. (참고문서: https://www.postgresql.org/docs/9.6/functions-json.html)


      이제 GIN 인덱스를 추가해 줍니다.

      CREATE INDEX idx__cards_data_tags ON cards USING gin ((data->'tags'));


      다음 동일한 쿼리 플랜을 출력해 봅니다.

      explain ANALYZE
SELECT count(*) FROM cards
WHERE
  data->'tags' ? 'Clean'
  AND data->'tags' ? 'Kitchen';


Aggregate (cost=20.03..20.04 rows=1 width=0) (actual time=2.665..2.666 rows=1 loops=1) -> Bitmap Heap Scan on cards (cost=16.01..20.03 rows=1 width=0) (actual time=0.788..2.485 rows=1537 loops=1)
    Recheck Cond: (((data -> 'tags'::text) ? 'Clean'::text) AND ((data -> 'tags'::text) ? 'Kitchen'::text))
    Heap Blocks: exact=185
    -> Bitmap Index Scan on idx__cards_data_tags (cost=0.00..16.01 rows=1 width=0) (actual time=0.750..0.750 rows=1537 loops=1)
        Index Cond: (((data -> 'tags'::text) ? 'Clean'::text) AND ((data -> 'tags'::text) ? 'Kitchen'::text))
Planning time: 0.088 ms
Execution time: 2.706 ms
(8 rows)
Time: 3.248 ms


      GIN 인덱스를 추가한 결과를 보면 속도가 빨라진 것을 볼 수 있으며 저장된 데이터가 많아질수록 더 눈에 띕니다.


      GIN 인덱스는 JSON 컬럼 자체에 인덱싱을 할 수 있어서 데이터 질의에 좀 더 유연함을 제공합니다.

      CREATE INDEX idx__cards_data ON cards USING gin (data);
SELECT count(*) FROM cards
WHERE
  data @> '{"tags": ["Clean", "Kitchen"]}'; 


Time: 2.837 ms


      저작자표시

      관련글 관련글 더보기

      댓글

    티스토리툴바