[Oracle] 오라클 JSON_EXISTS 함수 사용법

오라클 SQL에서 JSON 데이터 내에 특정 키나 값이 존재하는지 여부를 판단하기 위해서는 JSON_EXISTS 함수를 사용하면 된다. 이 함수는 JSON 문서에서 루트($), 배열 인덱스, 키 이름, 조건 등을 이용해 특정 경로를 지정하고, 해당 경로에 값이 존재하는지를 TRUE 또는 FALSE로 반환한다. 배열 안에 특정 요소가 포함되어 있는지 확인하거나, JSON 객체 내에 특정 키가 존재하는지 조건을 걸 때 유용하게 사용된다. JSON_EXISTS 함수는 WHERE, HAVING, CASE 등 다양한 조건절에 활용 가능하여, JSON 기반 데이터의 유효성 검증이나 조건 필터링에 매우 효과적이다.

 

목차 JSON_EXISTS 기본 사용법 JSON_EXISTS 활용법 JSON_EXISTS 배열에서 사용법

 

JSON_EXISTS 기본 사용법

JSON 데이터에서 특정 값이 존재하는지 체크

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

 

 

JSON_EXISTS("JSON 데이터", "조건")

 

JSON 데이터에서 DEPTNO 키 값이 일치하는 데이터가 존재하는지 체크하여, 존재하면 true를 반환하고 존재하지 않으면 false를 반환한다.

 

아래처럼 조건에 맞는 다양한 연산자를 사용하여 값을 체크하면 된다.

@ == 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

 

 

JSON 데이터에서 특정 키(KEY)가 존재하는지 체크하여, 존재하면 true를 반환하고 존재하지 않으면 false를 반환한다.

 

JSON_EXISTS 활용법

CASE 표현식에서 사용하는 방법

WITH emp_json AS (
  SELECT 7698 empno, CAST('{"EMPNO":7698,"ENAME":"BLAKE","DEPTNO":30}' AS VARCHAR2(4000)) emp_data FROM dual UNION ALL
  SELECT 7782 empno, CAST('{"EMPNO":7782,"ENAME":"CLARK","DEPTNO":20}' AS VARCHAR2(4000)) emp_data FROM dual UNION ALL
  SELECT 7566 empno, CAST('{"EMPNO":7566,"ENAME":"JONES","DEPTNO":20}' AS VARCHAR2(4000)) emp_data FROM dual
)  

SELECT e.empno
     , CASE WHEN JSON_EXISTS(e.emp_data, '$.DEPTNO?(@ == 20)') THEN 'true'
            ELSE 'false'
       END AS deptno_exists
  FROM emp_json e

 

 

CASE 표현식에서 조건으로 사용할 수 있다.

JSON 데이터에서 DEPTNO 키 값이 "20"이 존재하면 true 존재하지 않으면 false를 반환한다.

 

WHERE 절에서 사용하는 방법

WITH emp_json AS (
  SELECT 7698 empno, CAST('{"EMPNO":7698,"ENAME":"BLAKE","DEPTNO":30}' AS VARCHAR2(4000)) emp_data FROM dual UNION ALL
  SELECT 7782 empno, CAST('{"EMPNO":7782,"ENAME":"CLARK","DEPTNO":20}' AS VARCHAR2(4000)) emp_data FROM dual UNION ALL
  SELECT 7566 empno, CAST('{"EMPNO":7566,"ENAME":"JONES","DEPTNO":20}' AS VARCHAR2(4000)) emp_data FROM dual
)  

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

 

 

WHERE 절에서 조건으로 사용할 수 있다.

JSON 데이터에서 DEPTNO 키 값이 "20"인 데이터만 조회한다.

 

GROUP BY ~ HAVING 절에서 사용하는 방법

WITH emp_json AS (
  SELECT 7698 empno, CAST('{"EMPNO":7698,"ENAME":"BLAKE","SAL":2850,"DEPTNO":30}' AS VARCHAR2(4000)) emp_data FROM dual UNION ALL
  SELECT 7782 empno, CAST('{"EMPNO":7782,"ENAME":"CLARK","SAL":2450,"DEPTNO":20}' AS VARCHAR2(4000)) emp_data FROM dual UNION ALL
  SELECT 7566 empno, CAST('{"EMPNO":7566,"ENAME":"JONES","SAL":2975,"DEPTNO":20}' AS VARCHAR2(4000)) emp_data FROM dual
)  

SELECT JSON_VALUE(e.emp_data, '$.DEPTNO')   AS deptno
     , SUM(JSON_VALUE(e.emp_data, '$.SAL')) AS sal_sum
  FROM emp_json e
 GROUP BY JSON_VALUE(e.emp_data, '$.DEPTNO') 
HAVING JSON_EXISTS(MAX(e.emp_data), '$.DEPTNO?(@ > 20)')

 

 

GROUP BY ~ HAVING 절에서 조건으로 사용할 수 있다.

HAVING 절에서는 JSON 데이터를 MAX( MAX(e.emp_data) )로 잡아줘야 한다.

 

JSON_EXISTS 배열에서 사용법

배열에서 특정 값이 존재하는지 체크

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

 

 

배열에서 특정 값이 존재하는지 체크하여, 존재하면 true 존재하지 않으면 false를 반환한다.

 

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

 

 

JSON 데이터에서 키 값에 배열이 있을 경우, 배열에서 값이 존재하는지 체크할 수 있다.

 

배열에서 특정 배열 순번이 존재하는지 체크 방법

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

 

 

배열에서 특정 배열 순번(INDEX)이 존재하는지 체크하여, 존재하면 true 존재하지 않으면 false를 반환한다.

 

중첩 JSON 구조에서 값이 존재하는지 체크 방법

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 데이터에서 특정 값이 존재하는지 체크할 수 있다.