SQL - PostgreSQL, JSON Column

PostgreSQL

 

 

✅ JSON Column이란?

---

테이블 안에 JSON 형식 데이터를 그대로 저장하는 Column

MySQL과 PostgreSQL 모두 JSON 데이터 타입으로 저장하는 걸 허용한다.

 

SQL이면서 동시에 NoSQL처럼 동작한다.

 

사용 시기: 유연한 구조가 필요할 때(어떠한 column에 어떤 데이터가 들어가야 할 지 모를 때)

유연성↑, 구조 안정성↓

 

 

✅ JSON, JSONB

---

거의 동일한 값을 가진다.

차이점은 효율성이다.

JSON vs JSONB

JSON: 입력된 text를 그대로 복사해서 저장함

JSONB: 분해된 Binary 형식으로 저장함(데이터 입력 속도가 약간 느려진다. JSON에서 Binary로 변환해야 하기 때문, 처리 속도는 엄청 빨라짐, 대부분 JSONB 선호)

 

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

INSERT INTO
	users (profile)
VALUES
	(json_build_object('name', 'Taco', 'age', 30, 'city', 'Budapest')),
	(json_build_object('name', 'Giga', 'age', 25, 'city', 'Tbilisi', 'hobbies', json_build_array('reading', 'climbing')));

 

json_build_object, json_build_array

인자에 알맞은 값을 주면 각각 object와 array를 만들어준다.

 

https://www.postgresql.org/docs/current/datatype-json.html

8.14. JSON Types

 

 

✅ Querying JSON

---

SELECT
	profile -> 'name',
	profile -> 'age',
	profile -> 'city'
FROM
	users;

 

 

SELECT
	profile ->> 'name',
	profile ->> 'age',
	profile ->> 'city'
FROM
	users;

JSON 형식으로 저장되어서 붙은 " " 없애기

 

SELECT
	profile ->> 'name' AS name,
	profile ->> 'age' AS age,
	profile ->> 'city' AS city,
	profile -> 'hobbies' ->> 0 AS first_hobby
FROM
	users;

JSON 내부의 배열 열기, Column 이름 정해주기

 

Filtering Data on JSON Column

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

profile에 hobbies key가 있는 데이터만 가져옴

 

SELECT
	profile ->> 'name' AS name,
	profile ->> 'age' AS age,
	profile ->> 'city' AS city,
	profile -> 'hobbies' ->> 0 AS first_hobby
FROM
	users
WHERE
	profile -> 'hobbies' ? 'climbing';

hobbies에 climbing을 가지고 있는 데이터만 가져옴

 

SELECT
	profile ->> 'name' AS name,
	profile ->> 'age' AS age,
	jsonb_array_length(profile -> 'hobbies') AS total_hobbies,
	profile -> 'hobbies' ->> 0 AS first_hobby
FROM
	users
WHERE
	(profile ->> 'age')::INTEGER < 30;

profile에서 age를 가져왔을 때 text이기 때문에 integer로 변환시켜줘야 한다.

그리고 jsonb를 사용하고 있으면 jsonb 함수를 사용해야 한다.

 

?|

SELECT
	profile ->> 'name' AS name,
	profile ->> 'age' AS age,
	jsonb_array_length(profile -> 'hobbies') AS total_hobbies,
	profile -> 'hobbies' ->> 0 AS first_hobby
FROM
	users
WHERE
	profile -> 'hobbies' ?| ARRAY['reading', 'traveling'];

?| 연산자는 뒤에 주어지는 조건이 어느 것 하나라도 포함되면 만족되는 or 연산자이다.

 

?&

SELECT
	profile ->> 'name' AS name,
	profile ->> 'age' AS age,
	jsonb_array_length(profile -> 'hobbies') AS total_hobbies,
	profile -> 'hobbies' ->> 0 AS first_hobby
FROM
	users
WHERE
	profile -> 'hobbies' ?& ARRAY['reading', 'traveling'];

and 연산자

 

SELECT
	profile ->> 'name' AS name,
	profile ->> 'age' AS age,
	jsonb_array_length(profile -> 'hobbies') AS total_hobbies,
	profile -> 'hobbies' ->> 0 AS first_hobby,
	profile ->> 'city' AS city
FROM
	users
WHERE
	profile ->> 'city' LIKE 'B%';

LIKE 결합해서 사용

 

 

✅ Processing JSON

---

||

UPDATE users
SET
	profile = profile || jsonb_build_object('email', 'x@x.com');

json과 json을 받아서 합쳐진 json을 반환

 

-

UPDATE users
SET
	profile = profile - 'email'
WHERE
	profile ->> 'name' = 'Giga';

key와 value 제거

 

jsonb_set

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

인자로 target, path, new_value를 넣어주면 target의 데이터를 변경할 수 있다.(중첩된 데이터에 깊이 들어가 수정하고 싶을 때 사용)

마지막 인자로는 optional인 path에 해당하는 값이 부재일 때 새로 생성하는 여부를 나타내는 boolean 값이 있다.

(true이면 새 path로 생성, false면 해당하는 path가 없을 때 그냥 무시)

 

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

9.16. JSON Functions and Operators