[SQL] JSON 데이터를 다루는 SQL 함수

관계형 데이터베이스에서는 일반적으로 숫자나 문자열처럼 구조화된 데이터를 다루기 때문에,

대부분의 경우 SELECT,UPDATE 같은 SQL문만으로도 충분히 데이터를 조회하고 조작할 수 있다.

 

그런데 가끔 유연한 데이터 구조를 위해 JSON형식의 데이터를 컬럼에 저장하는 경우가 있다. 

이 경우에는 단순히 sql문을 써서 값을 조회하거나 조작하기가 쉽지않다.

그래서 sql 함수와 함께 쓰는 경우가 많다.

그럴때 MySQL에서 JSON 데이터를 다룰 때 알아야하는 SQL함수에 대해서 정리하고자 한다.

 

---

 

1. $.

$. 이 기호는 MySQL JSON 함수에서 경로(path)를 지정하는 문법으로 JSON 구조안에서 어느 key에 접근할지를 나타낸다.

$ 자체는 JSON 문서의 루트, 즉 가장 바깥 JSON객체를 의미하며 $.key는 루트 아래의 key, 즉 JSON문서 안의 key를 말한다.  

 

이러한 JSON 데이터가 있을 때,

{
  "name": "Alice",
  "age": 25,
  "address": {
    "city": "Seoul"
  }
  "skills" : ["SQL", "Java" , "Python"]
}

 

$.name 하면 결과값이 "Alice",

$.address.city 하면 결과값이 "Seoul",

$.skills[2]  하면  결과값이 "Python" 가 나온다.

이런식으로 key뿐만 아니라 배열도 접근이 가능하다.

 

2. JSON_EXTRACT(JSON데이터, '$.key')  혹은  ->(연산자)

 

JSON에서 값을 꺼내는 함수

JSON데이터에서 key를 기준으로 그에 대응하는 value를 꺼내는 함수다.

주로 JSON데이터 자리에는 JSON데이터가 저장 되어있는 칼럼명이 들어온다.

CREATE TABLE user (
  id INT,
  profile JSON
);

-- 샘플 데이터 입력
INSERT INTO user VALUES
(1, '{"name": "Alice", "age": 25, "skills": ["SQL", "Java"]}'),
(2, '{"name": "Bob", "age": 30, "skills": ["Python", "C++"]}')

예를 들어, 위의 쿼리를 보면 user라는 테이블에 id칼럼은 int 데이터가 들어가고

profile칼럼에는 JSON데이터가 들어가는 것을 알 수 있다.

이때, 아래와 같은 쿼리를 날리면,

SELECT JSON_EXTRACT(profile, '$.name') FROM user;

JSON 데이터가 저장된 profile 칼럼에서 name 키 값에 대응되는  "Alice"와 "Bob"이 조회된다.

SELECT profile->'$.name' From user;

JSON_EXTRACT를 매번 붙이기 귀찮다면 -> 연산자를 사용해도 된다. 똑같다.

->연산자의 의미는 JSON value를 그대로 가지고 오라는 의미다. 

"Alice" 와 "Bob"이 리턴된다. 

그런데 JSON 데이터에서는 결과값이 쌍따옴표까지 붙어서 나온다.

그래서 편하게 쌍따옴표를 뗀 문자열로 받을 수 있는데, 그것이 아래 함수다.

 

3.JSON_UNQUOTE(    ) 혹은 ->>(연산자)

 

JSON 쿼리를 통해 반환된 값은  " "(쌍따옴표)를 붙인상태로 오기 때문에 쌍따옴표를 벗겨내는 함수

위의 쿼리를JSON_UNQUOTE로 한번 더 감싸면 " Alice", "Bob" 이 아닌, Alice, Bob을 리턴받을 수 있다. 

SELECT JSON_UNQUOTE(JSON_EXTRACT(profile,'$.name')) FROM user;

하지만 이러면 매번 귀찮다. 

그래서 JSON_UNQUOTE와  JSON_EXTRACT를 합친 연산자가 있는데 그것이 ->> 다.

->> 연산자의 의미는 JSON value에서 " " 쌍따옴표를 뺀 문자열 그대로 추출하라는 의미다.

SELECT profile->>'$.name" FROM user;

 

이렇게 해도 똑같이 Alice,Bob을 리턴받을 수 있다.

 

4. JSON_OBJECT(key,value)

 

{key:value} 형식의 JSON을 생성하는 함수다.

JSON_OBJECT(key1,value1,key2,value2...)  이렇게 사용할 수 있다.

SELECT JSON_OBJECT('name','Alice','age',25);

하면, 

{ "name" : "Alice","age":25} 의 결과를 확인할 수 있다.

 

5. JSON_ARRAY(value1,value2,value3)

배열을 생성하는 함수다.

위의 JSON_OBJECT와 함께 쓰면 리스트가 포함된 JSON데이터를 만들 수 있다.

예를 들어, 아래와 같은 쿼리를 날리면

SELECT JSON_OBJECT('title','Spring강의','tags',JSON_ARRAY('Java','C','Spring'));

이런 결과가 나온다. JSON_ARRAY를 통해 여러 value를 함께 담을 수 있다.

{
  "title" : "Spring 강의",
  "tags" : ["Java","C","Spring"]
   }

 

6. JSON_SET(JSON데이터, '$.',value)

JSON 데이터 안에서 특정 key의 값을 추가하거나 수정하는 함수.

기존에 key가 있다면 덮어쓰고 없다면 새로 추가한다.

인자값은 차례로 JSON데이터, 수정하고자 하는 key경로, 바꿀값이다.

SELECT JSON_SET('{"name":"Alice", "age" :25}', $.age,30);

 

위의 쿼리를 실행하면 age에 대한 값25가 30으로 수정되는 것이다.

아래 쿼리를 실행하면 city key가 없으므로 추가된다.

SELECT JSON_SET('{"name":"Alice", "age" :25}', $.city,'Busan');

따라서 아래와 같이 조회된다.

{
  "name" : "Alice",
  "age" : 30,
  "city" : "Busan"
   }

실제론 아래와 같은 UPDATE 쿼리에 자주 사용된다.

profile칼럼이 JSON 데이터가 있는 칼럼이고.,

그중 active에 대한 value를 true로, updated_at을 현재시간으로 수정하는 데 사용되는 것이다.

UPDATE user
SET profile = JSON_SET(profile, '$.active', true, '$.updated_at', NOW())
WHERE id = 1;

 

 

7.JSON_CONTAINS()

JSON 데이터 안에 특정 값이나 구조가 포함되어있는지 확인해주는 함수로 1(true) 또는 0(false)값을 리턴한다.

예를 들어, 아래와 같은 쿼리는 user 테이블에서 json타입이 들어가는 profile칼럼을 대상으로 

{"verified" : "true"} 구조가 있는 유저를 조회하는 것이다.

SELECT * FROM user WHERE JSON_CONTAINS(profile,'{"verified" : true}')

혹은, 

SELECT * FROM user WHERE JSON_CONTAINS(tags,'"admin"')

이렇게 쓰면 user테이블에서  tags라는 칼럼에서  "admin"이라는 문자열이 담긴 유저를 조회하는 것이다. 

아래와 같은 테이블이 user테이블이라면 JSON형식의 배열이 담긴 tags칼럼에 "admin" 값을 가진 Alice가 조회되는 것이다.

 

 

 

 

 

8.JSON_TABLE

JSON 문서를 SQL의 테이블(행과 열)처럼 펼쳐주는 함수.

JSON은 하나의 값이기 때문에 WHERE 이나 JOIN 같은 SQL 연산을 하기가 힘들다.

그래서 이 JSON_TABLE() 로 JSON 내부를 풀어서 행과 컬럼 구조로 바꿔주는 역할을 한다.

 

문법 : JSON_TABLE(JSON객체(혹은 칼럼), $. PATH COLUMNS() AS table_name)

 

SELECT * FROM JSON_TABLE([{"id":1 , "name" : "A"}, {"id" : 2 , "name" : "B" }] , 
 	                     '$[*]' COLUMNS (id INT PATH '$.id',
                         				 name VARCHAR(10) PATH '$.name') AS jt

 

이렇게 하면 아래와 같은 결과를 얻는다

 

 

---

 

+

COALESCE(value1,value2....,valueN)

 

인자들을 왼쪽부터 차례로 평가해서 처음으로  NULL이 아닌 값을 반환하는 함수.

SELECT COALESCE(NULL,NULL,5,10) -->5반환