작년에 PostgreSQL의 JSON 데이터 타입 오퍼레이터라는 글에서 PostgreSQL 데이터베이스의 JSON 데이터타입에 대해서 간략하게 소개했다. 관계형 데이터베이스의 한계를 벗어난 기분에 JSON 데이터 타입을 마구 남용하다 보면 조만간 지옥을 맛보게 되기는 하지만 적절하게 사용하면 아주 편리한 기능 중 하나이다.1
PostgreSQL 9.4부터는 JSONB 데이터타입이 추가되었다. 문서에 따르면 Insert는 JSON보다는 느리지만, 바이너리 형식으로 저장되어서 처리가 훨씬 유연하다. 내가 써봤을 때는 특이한 조건이 아니라면 JSON 대신 JSONB를 안 쓸 이유가 없다. 대신 JSON이나 JSONB 형식이나 일반적인 관계형 데이터베이스와는 형식이 다르므로 처음 쓸 때 연산자나 함수에 대해서 익히는 시간이 좀 필요하다.
예를 들어 examples이라는 테이블에서 JSONB로 정의된 meta라는 칼럼에 다음과 같은 데이터가 들어있다고 해보자. JSONB 데이터 타입에 JSON을 보통 다루듯이 JSON 객체를 가진 배열을 넣은 것이다.
[{"id": 1, "image": "aaa.jpg"}, {"id": 2, "image": "ddd.jpg"}]
[{"id": 2, "image": "bbb.jpg"}, {"id": 5, "image": "ccc.jpg"}]
[{"id": 1, "image": "ccc.jpg"}, {"id": 3, "image": "bbb.jpg"}]
[{"id": 3, "image": "ddd.jpg"}, {"id": 6, "image": "aaa.jpg"}]
여기서 id가 1인 데이터를 가진 row를 조회하고 싶은 경우가 있다. 위에서 각 배열은 examples 테이블의 하나의 row인데 여기서 meta 칼럼에 배열을 하나 더 넣은 것이다. 왜 이러한 데이터베이스 디자인을 하는가는 별도의 이야기이고 이를 일반적인 데이터라고 생각하면 포함된 내용의 특정 값으로 조회하고자 하는 기능은 자연스러운 요구사항이다.
SELECT * from examples where meta @> '[{"id":1}]';
여기서는 @> 연산자를 사용했다. 문서에 따르면 JSON이 해당 값을 포함하고 있는지를 검사하는 연산자이다. 그러므로 meta 칼럼이 [{"id":1}]를 포함하고 있는지를 검사하는 쿼리이다. 이렇게 조회하면 다음과 같이 id가 1인 결과만 조회된다.
[{"id": 1, "image": "aaa.jpg"}, {"id": 2, "image": "ddd.jpg"}]
[{"id": 1, "image": "ccc.jpg"}, {"id": 3, "image": "bbb.jpg"}]
당연히 다음과 같이 image필드에 있는 값으로 조회할 수도 있다.
SELECT * from examples where meta @> '[{"image":"bbb.jpg"}]';
[{"id": 2, "image": "bbb.jpg"}, {"id": 5, "image": "ccc.jpg"}]
[{"id": 1, "image": "ccc.jpg"}, {"id": 3, "image": "bbb.jpg"}]
Comments