CREATE TABLE JSON (DATA JSON) ;
INSERT INTO JSON VALUES ('{"id": "1", "name": "LEE"}'),
('{"id": "2", "name": "PARK"}'),
('{"id": "3", "name": "CHO"}'),
('{"id": "4", "name": "CHOI"}'),
('{"id": "5", "name": "SEO", "update": "True"}') ;
SELECT *
FROM JSON ;
data
----------------------------------------------
{"id": "1", "name": "LEE"}
{"id": "2", "name": "PARK"}
{"id": "3", "name": "CHO"}
{"id": "4", "name": "CHOI"}
{"id": "5", "name": "SEO", "update": "True"}
(5 rows)
JSON 타입을 조회할 때 '->' 와 '->>' 를 사용할 수 있다.
update 키만 존재하는 값을 조회하고 싶을 때 아래와 같이 쿼리문을 작성합니다.
SELECT *
FROM JSON
WHERE DATA -> 'update' IS NOT NULL ;
data
----------------------------------------------
{"id": "5", "name": "SEO", "update": "True"}
(1 row)
update 키 값의 값만 조회하고 싶을 때 아래와 같이 쿼리문을 작성합니다.
SELECT DATA -> 'update' AS UPDATE
FROM JSON
WHERE DATA -> 'update' is not null ;
update
--------
"True"
(1 row)마찬가지로 '->>' 연산자를 통해 update 키가 존재하는 값을 조회할 수 있습니다.
SELECT DATA ->> 'update' AS UPDATE
FROM JSON
WHERE DATA ->> 'update' is not null ;
update
--------
True
(1 row)그럼 '->' 연산자와 '->>' 연산자의 차이는 뭘까요?
바로 조회되는 값의 type 차이입니다. '->' 로 조회된 값의 결과는 jsonb 타입, '->>'으로 조회된 쿼리는 text 타입으로 조회됩니다. 확인해보죠.
SELECT DATA -> 'update' AS UPDATE1,
DATA ->> 'update' AS UPDATE2
FROM JSON
WHERE DATA -> 'update' is not null ;
update1 | update2
---------+---------
"True" | True
(1 row)
JSON 타입 중 값이 리스트 형태로 되어있는 경우도 있습니다.
이 경우, 리스트의 값을 조회하거나 조건을 적용하고싶을 때 어떤 쿼리문을 이용할 수 있을지 확인해봅니다.
우선, 값이 list 로 되어있는 데이터를 생성합니다.
CREATE TABLE JSON2 (DATA JSON) ;
INSERT INTO JSON2 VALUES
('{"age": "21", "name": "LEE", "friend": ["KIM", "PARK"]}'),
('{"age": "22", "name": "KIM", "friend": ["LEE", "PARK"]}'),
('{"age": "15", "name": "PARK", "friend": ["KIM"], "update": "True"}') ;
friend 값 리스트의 첫 번째 값이 'KIM' 인 값을 조회합니다.
SELECT *
FROM JSON2
WHERE DATA -> 'friend' ->> 0 = 'KIM' ;
data
--------------------------------------------------------------------
{"age": "21", "name": "LEE", "friend": ["KIM", "PARK"]}
{"age": "15", "name": "PARK", "friend": ["KIM"], "update": "True"}
(2 rows)간단하게 해석을 해보자면 DATA 내에 friend 키의 0번째 값을 조회하는 과정이 'DATA -> 'friend' ->> 0' 이 부분입니다. 앞에서 말했듯 ->> 로 조회를 실행했으므로 결과는 text 값으로 나옵니다. 따라서 조건 절에 ' = 'KIM' '이 붙을 수 있는 것입니다.
그럼 반대로 '->'로 조회했을 때 jsonb 타입으로 결과를 반환하는데, 이 값에 처리를 하여 같은 조건절을 사용할 수 있지 않을까요? 굳이 이런 방법을 사용할 필요는 없겠지만 기술적으로 구현이 되는지에 대한 궁금증을 중점으로 테스트해봅니다.
SELECT *
FROM JSON2
WHERE (DATA -> 'friend' -> 0)::text = '"KIM"' ;
data
--------------------------------------------------------------------
{"age": "21", "name": "LEE", "friend": ["KIM", "PARK"]}
{"age": "15", "name": "PARK", "friend": ["KIM"], "update": "True"}
(2 rows)
'=' 등호를 사용하기 위해서 우선 타입캐스트가 필요하고, "KIM" 자체에서 텍스트로 변환되기 때문에 = '"KIM"' 으로 조회를 실행합니다.
굳이 이런 방법을 사용할 것 같진 않고, 위의 '->>' 연산자를 이용할 것 같습니다. 하지만 복잡한 쿼리나, 파이썬이나 자바의 라이브러리를 통해 데이터를 조회할 때 불가피하게 다른 방법을 사용할 때가 종종 있습니다. 이럴 때를 대비하여 다양한 경우를 알아두는 것이 좋아 보입니다.
DB Engineer