Skip to content

PostgreSQL JSON Functions and Operators #
Find similar titles

Structured data

Category
Programming

JSON Functions and Operators #

PostgreSQL/JSON Types에서 JSON 데이터 타입으로 컬럼을 만들고 데이터를 저장하고 조회했다. 조금 더 자세하고 편리하게 질의하기 위해 JSON Functions를 사용한다. 자주 쓰는 Function은 Operator라는 연산자를 통하여 간편하게 조회할 수 있다. 9.2 버전 이후로 JSON Function과 Operators가 새롭게 추가되고 있으니 PostgreSQL JSON Document를 살펴보는 것이 좋다.

JSON Operators #

Operator Right Operand Type Return type Description Example Example Result
-> int json or jsonb Get JSON array element (indexed from zero, negative integers count from the end) '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 {"c":"baz"}
-> text json or jsonb Get JSON object field by key '{"a": {"b":"foo"}}'::json->'a' {"b":"foo"}
->> int text Get JSON array element as text '[1,2,3]'::json->>2 3
->> text text Get JSON object field as text '{"a":1,"b":2}'::json->>'b' 2
#> text[] json or jsonb Get JSON object at the specified path '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' {"c": "foo"}
#>> text[] text Get JSON object at the specified path as text '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' 3

먼저 '->' 또는 '->>' 객체나 배열에서 값을 조회할 때 사용한다. 조회하려는 JSON 객체의 키를 통해 "PHYSICL_EXMN -> 'height'"과 같이 질의하면 아래와 같이 height의 값인 "{"h": 176, "d": "2019-01-02"}…" 값을 조회할 수 있다. (patient 테이블 생성 및 데이터 생성은 PostgreSQL/JSON Types 편을 참고한다.)

SELECT PHYSICL_EXMN -> 'height'
FROM PATIENT
#reuslt : [
    {"d": "2019-01-02","h": 176},
    …
]

Integer 데이터 타입으로 "PHYSICL_EXMN -> 'height' -> 0"과 같이 질의하면 JSON Array 객체 안에서 배열의 인덱스로 원하는 데이터를 조회할 수 있다. '->' 연산자와 '->>' 연산자의 큰 차이점은 반환되는 데이터의 타입이 각각 JSON 객체와 Text 타입이라는 점이다.

SELECT PHYSICL_EXMN -> 'height' -> 0
FROM PATIENT
#reuslt : { "d": "2019-01-02", "h": 176 }

JSON 객체에서 질의하려는 값이 복잡하거나 깊이 있다면(예를 들어 위의 예에서 "d" 값을 조회하고 싶다면), "PHYSICL_EXMN -> 'height' -> 0 -> 'd'" 라고 질의하여도 되지만 '#>' 또는 '#>>' 연산자를 통해 경로를 지정하여 질의할 수도 있다.

SELECT PHYSICL_EXMN #> '{height, 0, d}'
FROM PATIENT
#reuslt : "2019-01-02"

이외에도 JSON 데이터 타입 대신 JSONB 데이터 타입을 사용하면 더 다양한 연산자를 통하여 질의할 수 있다.

Operator Right Operand Type Description Example
@> jsonb Does the left JSON value contain the right JSON path/value entries at the top level? '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
<@ jsonb Are the left JSON path/value entries contained at the top level within the right JSON value? '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
? text Does the string exist as a top-level key within the JSON value? '{"a":1, "b":2}'::jsonb ? 'b'
? text[] Do any of these array strings exist as top-level keys?
?& text[] Do all of these array strings exist as top-level keys? '["a", "b"]'::jsonb ?& array['a', 'b']
jsonb
- text Delete key/value pair or string element from left operand. Key/value pairs are matched based on their key value. '{"a": "b"}'::jsonb - 'a'
- text[] Delete multiple key/value pairs or string elements from left operand. Key/value pairs are matched based on their key value. '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]
- integer Delete the array element with specified index (Negative integers count from the end). Throws an error if top level container is not an array. '["a", "b"]'::jsonb - 1
#- text[] Delete the field or element with specified path (for JSON arrays, negative integers count from the end) '["a", {"b":1}]'::jsonb #- '{1,b}'
@? jsonpath Does JSON path return any item for the specified JSON value? '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)'
@@ jsonpath Returns the result of JSON path predicate check for the specified JSON value. Only the first item of the result is taken into account. If the result is not Boolean, then null is returned. '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2'

? 연산자를 통해 객체 최상의 킷값에 weight 몸무게 정보가 존재하는지를 조회할 수 있다. 나머지 연산자들도 위의 표 또는 PostgreSQL JSON Document에서 찾아볼 수 있다.

SELECT physicl_exmn::jsonb ? `weight`
FROM patient
#reuslt : false

JSON Functions #

JSON Function은 일반적인 요소를 JSON 객체로 만드는 JSON Creation Function과 JSON 객체에서 질의하는 JSON Processing Functions로 나누어 알아보자. 본 글에서는 대표적인 Function 1개씩만 살펴보고 자세한 내용은 PostgreSQL JSON Document를 참조하자.

먼저 JSON 객체를 생성하는 함수 중 대표적인 json_build_object()를 사용하면 키와 값을 나열하여 JSON 객체를 만들 수 있다. 만약 반환되는 데이터 타입을 JSONB 데이터 타입으로 하고 싶다면 jsonb_build_object()를 사용하면 된다.

SELECT json_build_object(`h`,181,`d`,`2020-12-01`)
#reuslt : {"h" : 181, "d" : "2020-12-01"}

JSON 객체를 다양한 방식으로 처리하고 싶다면 JSON Processing Functions를 사용할 수 있다. 다음 질의문은 id 2의 모든 킷값을 조회하는 질의문이다.

SELECT json_object_keys(physicl_exmn)
FROM patient
WHERE id = 2
#reuslt :
        json_object_keys
        ----------------
        height
        weight

이외에도 유용한 Function이 존재하니 꼭 PostgreSQL JSON Document를 확인하자. PostgreSQL/JSON Path Language 편에서는 JSON path를 통한 질의 방법을 소개한다.

참고 출처 #

Incoming Links #

Related Data Sciences #

Suggested Pages #

0.0.1_20140628_0