SQL JSON연산자, JSONB 연산자

정의된 컬럼의 데이터가 아닌 임의 생성데이터이기 때문에 ::json으로 형변환 후 사용

JSON 연산자

json, jsonb 공통연산자

-> 연산자는 일반 리턴, ->> 연산자는 TEXT타입으로 리턴
json array는 "키 값" 대신 인덱스번호 사용가능

'{"키 값1": "밸류 값1", "키 값2": "밸류 값2"}' -> "키 값1" 하면 "밸류 값1" 리턴
select '{"p": {"1":"postgres"}, "s":{"1":"sql"}}'::json -> 'p' as result; ㅡ> {"1":"postgres"}

'{"키 값1": "밸류 값1", "키 값2": "밸류 값2"}' ->> "키 값1" 하면 TEXT타입으로 "밸류 값1" 리턴  

[{"키 값1": "밸류 값1"}, {"키 값2": "밸류 값2"}] -> 인덱스 번호0 하면 "밸류 값1" 리턴
[{"키 값1": "밸류 값1"}, {"키 값2": "밸류 값2"}] -> 인덱스 번호-1 하면 "밸류 값2" 리턴  
select '\[{"p":"postgres"}, {"s":"sql"}, {"m":"mongDB"}\]'::json -> 2 as result; ㅡ> {"m":"mongDB"}

\[{"키 값1": "밸류 값1"}, {"키 값2": "밸류 값2"}\] ->> 인덱스 번호1하면 TEXT타입으로 해당"밸류 값2" 리턴

#>, #>> 마찬가지로 일반리턴과 TEXT타입 리턴
#>, #>> 연산자는 다차원JSON 배열을 특정한 경로를 지정해 그 안의 밸류값을 리턴함.

'{"키 값1": "밸류 값1", "키 값2": "밸류 값2"}' #> '{"밸류 값1"까지 가는 경로}' 하면 "밸류 값1"리턴
select '{"post":[{"gre": {"sql":"do it"}},{"t":"sql"}]}'::json #> '{"post",0,"gre","sql"}' as result; ㅡ> "do it"

'{"키 값1": "밸류 값1", "키 값2": "밸류 값2"}' #>> '{"밸류 값1"까지 가는 경로}' 하면 TEXT타입으로 "밸류 값1"리턴

json array에 인덱스 번호응용도 같다.
select '[{"p":"postgre"},{"s":"sql"},{"a":"ai"}]'::json #> '{0,"p"}' as results; ㅡ> "postgres"

JSONB 연산자

# 배열의 포함관계. 집합의 포함기호 ⊃와 같은 의미
select '{"a":0, "b":1}'::jsonb @> '{"b":1}'::jsonb as result; ㅡ> true

# 특정 "키 값"이 최상위에 존재하는지 조회. 
select '{"username":"sumin", "contents":"hello world"}'::jsonb ? 'username' as result; ㅡ> true 

# 특정 "키 값"이 최상위에 1개이상 존재하는지 조회.
select '{"hello":0, "psql":1, "world":2}'::jsonb ?| array['a', 'psql'] as result; ㅡ> true

# 특정 "키 값"이 최상위에 모두 존재하는지 조회
select '{"hello":0, "psql":1, "world":2}'::jsonb ?& array['psql', 'world'] as result; ㅡ> true

# JSONB 병합
select '{"username":"minho"}'::jsonb || '{"contents":"hello"}'::jsonb as result; ㅡ>  {"contents": "hello", "username": "minho"}

# JSONB 원소삭제 
select '{"a": "0", "b": "1", "c": "2"}'::jsonb - 'b' as result; ㅡ>  {"a": "0", "c": "2"}

# JSONB 원소 1개 이상 삭제
select '{"a": "0", "b": "1", "c": "2"}'::jsonb - array['b','c'] as result; ㅡ>  {"a": "0"}

# JSON 배열 원소 삭제 with 인덱스
select '["a", "b", "c"]'::jsonb - 2 as result; ㅡ> ["a", "b"]