SQL에서 JSON 다루기

💡 이 내용 이후로 모든 SQL 관련 내용은 NomadCoder 강의를 듣고 정리한 글입니다.
아래의 링크에 SQL 학습에 필요한 데이터가 존재합니다.
https://nomadcoders.co/sql-masterclass

MySQL과 PostgreSQL에서는 JSON 데이터를 조회할 수 있다.

JSON 타입 컬럼을 가진 테이블 생성

CREATE TABLE users (
    user_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    profile JSONB
);

구분	설명
JSON	데이터를 JSON 형태로 저장
JSONB	데이터를 JSON 형태로 받지만 binary로 변환해서 저장한다. binary로 변환하는 과정이 있기에 저장할 때 시간이 더 걸린다. 하지만 데이터를 읽을 때는 JSONB가 더 빠르다.

•  

---

JSON 타입 컬럼에 데이터 입력

INSERT INTO users (profile)
VALUES
    ('{"name": "Nico", "age": 30, "city": "Berlin"}'),
    (json_build_object(
            'name', 'Lynn',
            'age', 25,
            'city', 'Korea',
            'hobbies', json_build_array('music', 'movies')
     ));

• json 데이터를 ‘ ‘ 로 감싸주고 key와 문자열 value는 “ “ 로 감싸야한다.
• ‘ ‘, “ “, { } 등을 실수로 빼먹을 경우 INSERT가 되지 않는다.
• 이를 방지하기 위해 json_build_object( ), json_build_array( )를 사용하면 편하다.

---

JSON 데이터 조회

{column_name} -> {json_key} 또는 {column_name} ->> {json_key}

SELECT
    profile ->> 'name' AS name,  -- -> 만 사용할 경우 " "로 감싸서 출력된다.
    profile -> 'age' AS age,
    profile -> 'hobbies' AS hobbies,
    profile -> 'hobbies' -> 0 AS first_hobby -- 리스트의 첫번째 값을 출력한다.
FROM users;

해당 방법으로 json에서 원하는 key만 조회를 할 수 있다.

해당 key가 존재하지 않을 경우 null로 출력된다.

---

JSON Update

데이터를 추가로 입력할 경우 {column_name} = {column_name} || {key : value}를 사용한다.

UPDATE users
SET profile = profile || '{"email": "123@wow.com"}'  -- 기존 데이터에 추가한다.
WHERE user_id = 1;

UPDATE users
SET profile = '{"email": "123@wow.com"}'  -- 기존 데이터가 사라지고 email 데이터만 남는다.
WHERE user_id = 1;

---

JSON Delete

JSON Key 제거

UPDATE users
SET profile = profile - 'email'  -- key 가 email인 데이터를 제거한다.
WHERE user_id = 1;

---

JSON Key의 리스트 원소 제거

jsonb_set( {column_name}, {key}, {query} )

UPDATE users
SET profile = profile || jsonb_set(
                         profile,
                         '{hobbies}',
                         -- hobbies 리스트에서 dancing 제거
                         profile -> 'hobbies' - 'dancing'  
                         );

---

JSON 관련 함수 & 연산자

https://www.postgresql.org/docs/9.5/functions-json.html

JSON Functions and Operators

JSON 연산자

함수	기능	예시
->	객체의 key에 접근	profile -> 'name'
->>	객체의 key 데이터를 문자열로 변환	profile ->> 'name'
?	해당 배열 또는 객체에 데이터가 존재하는지 확인	profile ? 'hobbies' profile -> 'hobbies' ? 'music'
|	여러 데이터 중 하나 이상 존재할 경우	profile -> 'hobbies' ?| array[’music’, ‘reading’] profile ?| array[’age’, ‘city’]
&	여러 데이터 중 전부 존재할 경우	profile -> 'hobbies' ?& array[’music’, ‘reading’] profile ?& array[’age’, ‘city’]
||	기존 json 데이터에 추가로 데이터 입력	profile = profile || '{"email": "123@wow.com"}'
-	key나 리스트의 원소를 기준으로 데이터 제거	profile = profile - 'email'

---

특정 Key가 존재하는지 여부

SELECT
    profile ->> 'name' AS name,
    profile -> 'age' AS age,
    profile -> 'hobbies' AS hobbies
FROM users
WHERE profile ? 'hobbies';

key에 hobbies가 존재하는 데이터만 출력한다.

---

Key의 리스트에 특정 데이터가 있는지

SELECT
    profile ->> 'name' AS name,
    profile -> 'age' AS age,
    profile -> 'hobbies' AS hobbies
FROM users
WHERE profile -> 'hobbies' ? 'music';

key의 hobbies 리스트에 music이 존재하는지 조회한다.

---

Key 리스트의 길이 출력

column 타입이 JSONB일 경우 jsonb_ 함수를 사용해야한다.

SELECT
    jsonb_array_length(profile -> 'hobbies') AS total_hobbies  -- hobbies 리스트의 길이
FROM users;

---

Key에 특정 데이터가 존재하는지 조회

SELECT
    profile ->> 'name' AS name
FROM users
-- -> 를 사용하면 문자열로 출력되지 않으므로 에러가 발생
WHERE profile ->> 'name' = 'Nico';

---

숫자 비교

SELECT
    profile ->> 'name' AS name
FROM users
-- ( ) :: {type}을 사용하여 형변환을 해야한다.
WHERE (profile ->> 'age') :: integer < 30;

---

JSON 함수

json_set, jsonb_set

UPDATE users
SET profile = profile || jsonb_set(
                         profile,
                         '{hobbies}',
                         profile -> 'hobbies' - 'dancing'  
                         );

profile 컬럼의 hobbies Key의 값을 hobbies 리스트에서 dancing을 뺀 리스트로 저장

SET profile = profile || jsonb_set(
        profile, '{hobbies}', 
        profile -> 'hobbies' || jsonb_build_array('cooking')
                         )
WHERE user_id = 1;

profile 컬럼의 hobbies Key의 값을 hobbies 리스트에서 cooking이 추가된 리스트로 저장

---

연습 문제

hobbies를 기준으로 해당 취미를 가지고 있는 사람이 몇 명인지 출력

SELECT
    jsonb_array_elements_text(profile -> 'hobbies'),
    count(*)
FROM users
GROUP BY jsonb_array_elements_text(profile -> 'hobbies');