오라클 JSON_EXISTS 함수 완벽 가이드: JSON 데이터 유효성 검사부터 WHERE 조건절 활용까지

 

현대의 데이터베이스 환경은 점점 더 반정형 데이터(Semi-structured Data)를 많이 다루게 됩니다. 특히 웹, 모바일, IoT 시스템에서는 JSON(JavaScript Object Notation) 형식으로 데이터를 저장하거나 교환하는 경우가 매우 많습니다. 이러한 트렌드에 맞춰 Oracle Database는 JSON 데이터를 보다 효율적으로 처리할 수 있는 여러 함수를 제공하고 있으며, 그중에서도 오늘 소개할 JSON_EXISTS 함수는 JSON 문서에서 특정 값이나 키가 존재하는지 여부를 손쉽게 판단할 수 있도록 도와주는 핵심 함수입니다.

이 포스팅에서는 오라클 SQL에서 JSON_EXISTS 함수의 기본 사용법부터 고급 활용 예제, 배열 처리 및 중첩 구조 탐색 방법까지 자세하고 체계적으로 정리하였습니다. 실무에서 바로 사용할 수 있도록 실제 SQL 예제와 함께 설명하였으며, 다양한 조건절에서 어떻게 활용할 수 있는지도 함께 소개합니다.

---

📌 JSON_EXISTS 함수란?

JSON_EXISTS 함수는 Oracle SQL에서 JSON 문서 내에 특정 경로(Path)에 값이 존재하는지를 TRUE 또는 FALSE로 반환하는 함수입니다. 다음과 같은 경우에 유용하게 사용됩니다:

• JSON 객체 내에 특정 키가 존재하는지 확인
• JSON 배열 안에 특정 요소가 포함되어 있는지 확인
• WHERE, CASE, HAVING 조건절에서 JSON 기반 조건 필터링
• JSON 문서의 유효성 검증이나 데이터 정합성 체크

구문은 다음과 같습니다.

JSON_EXISTS(json_data, path_condition)

---

✅ JSON_EXISTS 기본 사용법

✔️ 특정 값이 존재하는지 확인

SELECT CASE WHEN JSON_EXISTS('{"ENAME":"BLAKE","DEPTNO":30}', '$.DEPTNO?(@ == 30)') THEN
                 'true'
            ELSE 'false'
       END AS result
FROM dual;

📌 해석:

• $.DEPTNO?(@ == 30)는 DEPTNO 값이 30인 경우 true를 반환합니다.
• @은 현재 위치의 값을 의미합니다.
• 결과: true

✔️ 비교 연산자 활용

다양한 비교 연산자를 사용할 수 있습니다.

• @ == 20 : 값이 20인지 체크
• @ > 20 : 값이 20보다 큰지 체크
• @ >= 20 : 20 이상인지 체크

---

✅ JSON에서 특정 키(Key) 존재 여부 확인

SELECT CASE WHEN JSON_EXISTS('{"ENAME":"BLAKE","DEPTNO":30}', '$.ENAME') THEN
                 'true'
            ELSE 'false'
       END AS result1,
       CASE WHEN JSON_EXISTS('{"ENAME":"BLAKE","DEPTNO":30}', '$.JOB') THEN
                 'true'
            ELSE 'false'
       END AS result2
FROM dual;

• $.ENAME 경로가 존재 → true
• $.JOB 키는 존재하지 않음 → false

👉 단순히 값 비교가 아닌 키 존재 여부만 판단할 수도 있다는 점에서 매우 유용합니다.

---

✅ 조건절에서 JSON_EXISTS 활용법

1. CASE 문에서 사용하기

WITH emp_json AS (
  SELECT 7698 empno, '{"EMPNO":7698,"ENAME":"BLAKE","DEPTNO":30}' emp_data FROM dual UNION ALL
  SELECT 7782 empno, '{"EMPNO":7782,"ENAME":"CLARK","DEPTNO":20}' emp_data FROM dual
)
SELECT empno,
       CASE WHEN JSON_EXISTS(emp_data, '$.DEPTNO?(@ == 20)') THEN 'true'
            ELSE 'false' END AS dept_20_yn
FROM emp_json;

2. WHERE 절에서 사용하기

SELECT empno,
       JSON_VALUE(emp_data, '$.ENAME') AS ename,
       JSON_VALUE(emp_data, '$.DEPTNO') AS deptno
FROM emp_json
WHERE JSON_EXISTS(emp_data, '$.DEPTNO?(@ == 20)');

🔍 JSON_EXISTS를 WHERE 절에 사용하면, JSON 내부 특정 조건을 만족하는 데이터만 필터링할 수 있어 매우 강력한 조건절로 활용 가능합니다.

3. GROUP BY ~ HAVING 절에서 사용하기

SELECT JSON_VALUE(emp_data, '$.DEPTNO') AS deptno,
       SUM(JSON_VALUE(emp_data, '$.SAL')) AS sal_sum
FROM (
  SELECT '{"EMPNO":7698,"ENAME":"BLAKE","SAL":2850,"DEPTNO":30}' emp_data FROM dual UNION ALL
  SELECT '{"EMPNO":7782,"ENAME":"CLARK","SAL":2450,"DEPTNO":20}' emp_data FROM dual UNION ALL
  SELECT '{"EMPNO":7566,"ENAME":"JONES","SAL":2975,"DEPTNO":20}' emp_data FROM dual
) emp_json
GROUP BY JSON_VALUE(emp_data, '$.DEPTNO')
HAVING JSON_EXISTS(MAX(emp_data), '$.DEPTNO?(@ > 20)');

📌 주의: HAVING절에서는 집계 함수와 함께 JSON_EXISTS를 사용하려면 MAX(emp_data) 같은 방식으로 적용해야 합니다.

---

✅ JSON 배열에서 JSON_EXISTS 사용법

✔️ 단순 배열에서 특정 값 존재 확인

SELECT CASE WHEN JSON_EXISTS('["BLAKE","CLARK","JONES"]', '$?(@ == "JONES")') THEN
                 'true'
            ELSE 'false'
       END AS result
FROM dual;

• "JONES"라는 값이 배열 내에 존재 → true

✔️ JSON 키 안에 배열 존재 시

SELECT CASE WHEN JSON_EXISTS('{"ENAMES":["BLAKE","CLARK","JONES"]}', '$.ENAMES?(@ == "JONES")') THEN
                 'true'
            ELSE 'false'
       END AS result1,
       CASE WHEN JSON_EXISTS('{"ENAMES":["BLAKE","CLARK","JONES"]}', '$.ENAMES?(@ == "SCOTT")') THEN
                 'true'
            ELSE 'false'
       END AS result2
FROM dual;

• 배열 내 "JONES"는 존재 → true
• "SCOTT"은 없음 → false

---

✅ 배열 인덱스를 활용한 존재 여부 판단

SELECT CASE WHEN JSON_EXISTS('{"ENAMES":["BLAKE","CLARK","JONES"]}', '$.ENAMES[0]') THEN
                 'true'
            ELSE 'false'
       END AS result1,
       CASE WHEN JSON_EXISTS('{"ENAMES":["BLAKE","CLARK","JONES"]}', '$.ENAMES[4]') THEN
                 'true'
            ELSE 'false'
       END AS result2
FROM dual;

• 0번 인덱스 존재 → true
• 4번 인덱스 존재하지 않음 → false

---

✅ 중첩된 JSON 구조에서도 JSON_EXISTS 적용하기

SELECT CASE WHEN JSON_EXISTS('{
                "DEPT": [
                  {
                    "DEPTNO": 10,
                    "DNAME": "ACCOUNTING",
                    "EMP": [
                      {"EMPNO":7839,"ENAME":"KING"},
                      {"EMPNO":7782,"ENAME":"CLARK"}
                    ]
                  },
                  {
                    "DEPTNO": 20,
                    "DNAME": "RESEARCH",
                    "EMP": [
                      {"EMPNO":7566,"ENAME":"JONES"},
                      {"EMPNO":7788,"ENAME":"SCOTT"},
                      {"EMPNO":7902,"ENAME":"FORD"}
                    ]
                  }
                ]
              }', '$.DEPT.EMP?(@.ENAME == "JONES")') THEN 'true'
         ELSE 'false'
       END AS ename_exists
FROM dual;

📌 복잡한 구조의 JSON에서도 .DEPT.EMP 경로를 통해 중첩된 배열 내부까지 탐색할 수 있습니다.

---

✅ 마무리 정리: JSON_EXISTS 함수 핵심 포인트 요약

구분 사용 예 설명

기본 사용	JSON_EXISTS(json, '$.key')	키 존재 여부 확인
값 비교	JSON_EXISTS(json, '$.key?(@ == 30)')	값 조건 검사
배열 검사	JSON_EXISTS(json, '$?(@ == "value")')	배열 내 값 포함 여부 확인
조건절 활용	WHERE, CASE, HAVING	다양한 조건 필터링 가능
중첩 구조	$.DEPT.EMP?(@.ENAME == "JONES")	깊이 있는 탐색 가능

---

✨ 마무리하며: 실무에 바로 적용할 수 있는 JSON_EXISTS 활용

오늘 소개한 오라클 SQL의 JSON_EXISTS 함수는 JSON 기반 시스템에서 데이터 유효성 검사 및 조건 필터링을 수행할 때 강력한 도구입니다. 특히 최근에는 ERP, CRM, 빅데이터 분석 시스템 등에서 JSON 기반 데이터 처리가 많아지면서 이 함수의 활용도가 더욱 높아지고 있습니다.

• JSON 키/값 유무 체크
• WHERE/CASE/HAVING 절 조건문 작성
• 배열 및 중첩 구조 탐색