JSON 함수와 연산자

JSON 함수와 연산자 (JSON Functions and Operators)

JSON 데이터를 일반 SQL 데이터와 함께 다루고 싶다면, PostgreSQL의 JSON 지원 기능을 잘 아는 게 큰 도움이 돼요. JSON을 DB에 올리고, 관계형 데이터로 JSON을 만들고, SQL/JSON 쿼리 함수와 경로 언어로 조회하는 일까지 이 페이지에서 차근차근 정리해 볼게요.

출처: 공식문서

SQL/JSON 이란

SQL/JSON 표준은 트랜잭션 지원을 포함해 JSON 데이터를 일반 SQL 데이터와 함께 다룰 수 있게 해줘요. 크게 세 가지로 나눌 수 있어요.

JSON 데이터를 DB에 업로드해 일반 SQL 컬럼에 문자·이진 문자열로 저장하기
관계형 데이터에서 JSON 객체·배열 생성하기
SQL/JSON 쿼리 함수와 SQL/JSON 경로 언어 표현으로 JSON 데이터 조회하기

PostgreSQL이 지원하는 JSON 타입에 대한 자세한 내용은 관련 데이터 타입 섹션을 참고하세요.

`json` 과 `jsonb` 연산자

jsonb 에는 일반 비교 연산자(Table 9.1)도 사용 가능하지만 json 에는 그렇지 않아요. 비교 연산자는 B-tree 연산 순서 규칙을 따릅니다. (집계 함수 json_agg, json_object_agg 와 그 jsonb 대응인 jsonb_agg, jsonb_object_agg 도 함께 알아두면 좋아요.)

연산자	설명	예시
`json -> integer → json` / `jsonb -> integer → jsonb`	JSON 배열의 `n` 번째 요소를 추출해요. 배열 요소는 0부터 인덱싱되지만, 음수 정수는 끝에서부터 세요.	`'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> 2` → `{"c":"baz"}`, `-> -3` → `{"a":"foo"}`
`json -> text → json` / `jsonb -> text → jsonb`	주어진 키의 JSON 객체 필드를 추출해요.	`'{"a": {"b":"foo"}}'::json -> 'a'` → `{"b":"foo"}`
`json ->> integer → text` / `jsonb ->> integer → text`	JSON 배열의 `n` 번째 요소를 `text` 로 추출해요.	`'[1,2,3]'::json ->> 2` → `3`
`json ->> text → text` / `jsonb ->> text → text`	주어진 키의 필드를 `text` 로 추출해요.	`'{"a":1,"b":2}'::json ->> 'b'` → `2`
`json #> text[] → json` / `jsonb #> text[] → jsonb`	지정 경로의 JSON 하위 객체를 추출해요. 경로 요소는 필드 키나 배열 인덱스가 될 수 있어요.	`'{"a": {"b": ["foo","bar"]}}'::json #> '{a,b,1}'` → `"bar"`
`json #>> text[] → text` / `jsonb #>> text[] → text`	지정 경로의 하위 객체를 `text` 로 추출해요.	`'{"a": {"b": ["foo","bar"]}}'::json #>> '{a,b,1}'` → `bar`

참고로 이 필드/요소/경로 추출 연산자들은 JSON 입력이 요청과 맞는 구조가 아니면 실패하지 않고 NULL을 반환해요(예: 그런 키나 배열 요소가 없을 때).

`jsonb` 전용 추가 연산자

연산자	설명	예시
`jsonb @> jsonb → boolean`	첫 JSON 값이 두 번째를 포함하는지.	`'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb` → `t`
`jsonb <@ jsonb → boolean`	첫 JSON 값이 두 번째에 포함되는지.	`'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb` → `t`
`jsonb ? text → boolean`	문자열이 JSON 값 안에 최상위 키 또는 배열 요소로 존재하는지.	`'{"a":1, "b":2}'::jsonb ? 'b'` → `t`
`jsonb ?\| text[] → boolean`	텍스트 배열의 문자열 중 하나라도 최상위 키/요소로 존재하는지.	`'{"a":1, "b":2, "c":3}'::jsonb ?\| array['b', 'd']` → `t`
`jsonb ?& text[] → boolean`	텍스트 배열의 문자열이 전부 최상위 키/요소로 존재하는지.	`'["a", "b", "c"]'::jsonb ?& array['a', 'b']` → `t`
`jsonb \|\| jsonb → jsonb`	두 `jsonb` 값을 연결해요. 배열끼리는 전체 요소를 담은 배열, 객체끼리는 키의 합집합(중복 키는 두 번째 값)을 만들고, 그 외 경우는 비배열 입력을 단일 요소 배열로 바꿔 배열처럼 처리해요. 재귀적으로 동작하지 않고 최상위 배열·객체 구조만 병합돼요.	`'["a", "b"]'::jsonb \|\| '["a", "d"]'::jsonb` → `["a", "b", "a", "d"]`, `'{"a": "b"}'::jsonb \|\| '{"c": "d"}'::jsonb` → `{"a": "b", "c": "d"}`. 배열을 단일 항목으로 붙이려면 추가 배열 레이어로 감싸요: `'[1, 2]'::jsonb \|\| jsonb_build_array('[3, 4]'::jsonb)` → `[1, 2, [3, 4]]`
`jsonb - text → jsonb`	객체에서 키(및 값)를, JSON 배열에서 일치하는 문자열 값을 삭제해요.	`'{"a": "b", "c": "d"}'::jsonb - 'a'` → `{"c": "d"}`, `'["a", "b", "c", "b"]'::jsonb - 'b'` → `["a", "c"]`
`jsonb - text[] → jsonb`	왼쪽 피연산자에서 일치하는 모든 키/배열 요소를 삭제해요.	`'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]` → `{}`
`jsonb - integer → jsonb`	지정 인덱스(음수는 끝에서)의 배열 요소를 삭제해요. JSON 값이 배열이 아니면 오류를 내요.	`'["a", "b"]'::jsonb - 1` → `["a"]`
`jsonb #- text[] → jsonb`	지정 경로의 필드나 배열 요소를 삭제해요. 경로 요소는 키나 인덱스.	`'["a", {"b":1}]'::jsonb #- '{1,b}'` → `["a", {}]`
`jsonb @? jsonpath → boolean`	JSON 경로가 지정 JSON 값에 대해 어떤 항목이라도 반환하는지. (SQL 표준 JSON 경로 표현에만 유용하고, 항상 값을 반환하는 조건 검사 표현엔 안 써요.)	`'{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)'` → `t`
`jsonb @@ jsonpath → boolean`	지정 JSON 값에 대한 JSON 경로 조건 검사의 결과를 반환해요. (경로 결과가 단일 불리언이 아니면 NULL을 반환하므로 조건 검사 표현에만 유용해요.)	`'{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2'` → `t`

참고: jsonpath 연산자 @? 와 @@ 는 오류를 억제해요. 누락된 객체 필드나 배열 요소, 예상치 못한 JSON 항목 타입, 날짜·시간·숫자 오류 같은 것들이죠. 아래의 jsonpath 관련 함수들도 이런 오류 유형을 억제하도록 지시할 수 있어요. 이 동작은 구조가 다양한 JSON 문서 컬렉션을 검색할 때 유용해요.

JSON 생성 함수

여러 함수가 RETURNING 절을 지원하는데, 반환 타입을 지정하며 json, jsonb, bytea, 문자 문자열 타입(text·char·varchar), 또는 json 으로 캐스팅 가능한 타입 중 하나여야 해요. 기본은 json 타입이에요.

함수	설명
`to_json ( anyelement ) → json` / `to_jsonb ( anyelement ) → jsonb`	어떤 SQL 값도 `json`/`jsonb` 로 변환해요. 배열·복합 값은 재귀적으로 배열·객체로 변환되고(다차원 배열은 JSON의 배열의 배열), 그 외에는 SQL 타입에서 `json` 으로의 캐스트가 있으면 그걸 쓰고 없으면 스칼라 JSON 값을 만들어요. 숫자·불리언·널이 아닌 스칼라는 필요한 이스케이프를 붙여 유효한 JSON 문자열 값으로 텍스트 표현을 써요. 예: `to_json('Fred said "Hi."'::text)` → `"Fred said \"Hi.\""`
`array_to_json ( anyarray [, boolean ] ) → json`	SQL 배열을 JSON 배열로 변환해요. `to_json` 과 같지만 선택 불리언이 true면 최상위 배열 요소 사이에 줄바꿈을 추가해요. 예: `array_to_json('{{1,5},{99,100}}'::int[])` → `[[1,5],[99,100]]`
`json_array ( [ { value_expression [ FORMAT JSON ] } [, ...] ] [ { NULL \| ABSENT } ON NULL ] [ RETURNING ... ] )` / `json_array ( [ query_expression ] [ RETURNING ... ] )`	일련의 `value_expression` 이나 단일 컬럼을 반환하는 SELECT인 `query_expression` 의 결과에서 JSON 배열을 만들어요. `ABSENT ON NULL` 이 지정되면 NULL 값을 무시하는데, `query_expression` 을 쓰면 항상 그렇습니다. 예: `json_array(1,true,json '{"a":null}')` → `[1, true, {"a":null}]`
`row_to_json ( record [, boolean ] ) → json`	SQL 복합 값을 JSON 객체로 변환해요. `to_json` 과 같지만 선택 불리언이 true면 최상위 요소 사이에 줄바꿈을 추가해요. 예: `row_to_json(row(1,'foo'))` → `{"f1":1,"f2":"foo"}`
`json_build_array ( VARIADIC "any" ) → json` / `jsonb_build_array (...) → jsonb`	가변 인자 목록으로 (서로 다른 타입일 수 있는) JSON 배열을 만들어요. 각 인자는 `to_json`/`to_jsonb` 규칙으로 변환돼요. 예: `json_build_array(1, 2, 'foo', 4, 5)` → `[1, 2, "foo", 4, 5]`
`json_build_object ( VARIADIC "any" ) → json` / `jsonb_build_object (...) → jsonb`	가변 인자 목록으로 JSON 객체를 만들어요. 관례적으로 키와 값이 번갈아 나오고, 키는 text로 강제 변환, 값은 `to_json`/`to_jsonb` 규칙으로 변환돼요. 예: `json_build_object('foo', 1, 2, row(3,'bar'))` → `{"foo" : 1, "2" : {"f1":3,"f2":"bar"}}`
`json_object ( [ { key_expression { VALUE \| ':' } value_expression [ FORMAT JSON [ ENCODING UTF8 ] ] }[, ...] ] [ { NULL \| ABSENT } ON NULL ] [ { WITH \| WITHOUT } UNIQUE [ KEYS ] ] [ RETURNING ... ] )`	주어진 키/값 쌍 전부의 JSON 객체(없으면 빈 객체)를 만들어요. `key_expression` 은 `text` 로 변환되는 스칼라 표현식이고 NULL이거나 `json` 으로 캐스트되는 타입이면 안 돼요. `WITH UNIQUE KEYS` 를 지정하면 중복 `key_expression` 이 없어야 해요. `ABSENT ON NULL` 이면 값이 NULL인 쌍은 빼고, `NULL ON NULL` 이거나 절을 생략하면 키를 값 `NULL` 로 포함해요. 예: `json_object('code' VALUE 'P123', 'title': 'Jaws')` → `{"code" : "P123", "title" : "Jaws"}`
`json_object ( text[] ) → json` / `jsonb_object ( text[] ) → jsonb`	텍스트 배열로 JSON 객체를 만들어요. 배열은 짝수 멤버의 1차원(키/값 교대)이거나 각 내부 배열이 정확히 2개 요소(키/값 쌍)인 2차원이어야 해요. 모든 값은 JSON 문자열로 변환돼요. 예: `json_object('{a, 1, b, "def", c, 3.5}')` → `{"a" : "1", "b" : "def", "c" : "3.5"}`
`json_object ( keys text[], values text[] ) → json` / `jsonb_object (...) → jsonb`	별도의 키·값 배열에서 쌍으로 받아 객체를 만들어요. 나머지는 단일 인자 형태와 동일해요. 예: `json_object('{a,b}', '{1,2}')` → `{"a": "1", "b": "2"}`
`json ( expression [ FORMAT JSON [ ENCODING UTF8 ]] [ { WITH \| WITHOUT } UNIQUE [ KEYS ]] ) → json`	`text` 나 `bytea`(UTF8) 문자열로 지정된 표현식을 JSON 값으로 변환해요. `expression` 이 NULL이면 SQL 널을, `WITH UNIQUE` 를 지정하면 중복 객체 키가 없어야 해요. 예: `json('{"a":123, "b":[true,"foo"], "a":"bar"}')` → `{"a":123, "b":[true,"foo"], "a":"bar"}`
`json_scalar ( expression )`	SQL 스칼라 값을 JSON 스칼라 값으로 변환해요. 입력이 NULL이면 SQL 널, 숫자·불리언이면 대응하는 JSON 숫자·불리언, 그 외는 JSON 문자열을 반환해요. 예: `json_scalar(123.45)` → `123.45`
`json_serialize ( expression [ FORMAT JSON [ ENCODING UTF8 ] ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ] )`	SQL/JSON 표현식을 문자·이진 문자열로 변환해요. `expression` 은 어떤 JSON 타입·문자열 타입·UTF8 `bytea` 도 되고, `RETURNING` 의 반환 타입은 어떤 문자열 타입이나 `bytea` 도 돼요. 기본은 `text`. 예: `json_serialize('{ "a" : 1 } ' RETURNING bytea)` → `\x7b20226122203a2031207d20`

SQL/JSON 테스트 함수

expression IS [NOT] JSON [ { VALUE | SCALAR | ARRAY | OBJECT } ] [ { WITH | WITHOUT } UNIQUE [ KEYS ] ] — expression 을 JSON으로 파싱할 수 있는지(가능하면 지정 타입인지) 검사하는 술어예요. SCALAR/ARRAY/OBJECT 를 지정하면 그 특정 타입인지, WITH UNIQUE KEYS 를 지정하면 객체에 중복 키가 없는지도 함께 검사해요. 예:

     js     | json? | scalar? | object? | array?
------------+-------+---------+---------+--------
 123        | t     | t       | f       | f
 "abc"      | t     | t       | f       | f
 {"a": "b"} | t     | f       | t       | f
 [1,2]      | t     | f       | f       | t
 abc        | f     | f       | f       | f

JSON 처리 함수

배열·객체 펼치기와 키

json_array_elements ( json ) → setof json / jsonb_array_elements (...) → setof jsonb — 최상위 JSON 배열을 JSON 값 집합으로 펼쳐요. 예: json_array_elements('[1,true, [2,false]]') → 1, true, [2,false]
json_array_elements_text ( json ) → setof text / jsonb_array_elements_text (...) → setof text — 최상위 배열을 text 값 집합으로 펼쳐요. 예: json_array_elements_text('["foo", "bar"]') → foo, bar
json_array_length ( json ) → integer / jsonb_array_length (...) → integer — 최상위 배열의 요소 수를 반환해요. 예: json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') → 5
json_each ( json ) → setof record ( key text, value json ) / jsonb_each (...) → setof record ( key text, value jsonb ) — 최상위 객체를 키/값 쌍 집합으로 펼쳐요. 예: json_each('{"a":"foo", "b":"bar"}') → (a,"foo"), (b,"bar")
json_each_text ( json ) → setof record ( key text, value text ) / jsonb_each_text (...) → setof record ( key text, value text ) — value 가 text 인 버전. 예: json_each_text('{"a":"foo", "b":"bar"}') → (a,foo), (b,bar)
json_object_keys ( json ) → setof text / jsonb_object_keys (...) → setof text — 최상위 객체의 키 집합을 반환해요. 예: json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') → f1, f2

경로 추출

json_extract_path ( from_json json, VARIADIC path_elems text[] ) → json / jsonb_extract_path (...) → jsonb — 지정 경로의 JSON 하위 객체를 추출해요. (#> 연산자와 기능적으로 동일하지만 경로를 가변 인자 목록으로 쓰는 게 때로 더 편해요.) 예: json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6') → "foo"
json_extract_path_text ( from_json json, VARIADIC path_elems text[] ) → text / jsonb_extract_path_text (...) → text — text 로 추출하는 버전(#>> 와 동등). 예: json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6') → foo

레코드로 변환

json_populate_record ( base anyelement, from_json json ) → anyelement / jsonb_populate_record (...) → anyelement — 최상위 JSON 객체를 base 인자의 복합 타입 행으로 펼쳐요. JSON 객체에서 출력 행 타입의 컬럼 이름과 일치하는 필드를 찾아 값을 넣고, 일치하지 않는 필드는 무시해요. 일반적으로 base 는 그냥 NULL 을 줘서 일치하지 않는 컬럼은 널로 채우지만, base 가 NULL이 아니면 그 값이 일치하지 않는 컬럼에 쓰여요. JSON 값을 출력 컬럼의 SQL 타입으로 변환하는 규칙은 순서대로: JSON 널은 항상 SQL 널로 → 출력 컬럼이 json/jsonb 면 그대로 → 복합(행) 타입이고 JSON 객체면 재귀 적용 → 배열 타입이고 JSON 배열이면 재귀 적용 → JSON 문자열이면 컬럼 타입 입력 변환 함수에 문자열 내용 전달 → 그 외는 일반 텍스트 표현 전달. 예: json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}, "x": "foo"}') → (1,{2,"a b"},(4,"a b c"))
jsonb_populate_record_valid ( base anyelement, from_json json ) → boolean — jsonb_populate_record 가 주어진 JSON 객체로 오류 없이 끝날 수 있는지(true, 즉 유효 입력) 반환하는 테스트 함수예요. 예: char(2) 컬럼에 {"a": "aaa"} 를 넣으면 값이 너무 길어 오류가 나므로 false 가 돼요.
json_populate_recordset ( base anyelement, from_json json ) → setof anyelement / jsonb_populate_recordset (...) → setof anyelement — 최상위 JSON 객체 배열을 base 복합 타입의 행 집합으로 펼쳐요. 각 요소는 json[b]_populate_record 처럼 처리돼요. 예: json_populate_recordset(null::twoints, '[{"a":1,"b":2}, {"a":3,"b":4}]') → (1,2), (3,4)
json_to_record ( json ) → record / jsonb_to_record (...) → record — 최상위 객체를 AS 절이 정의한 복합 타입 행으로 펼쳐요. (record 를 반환하는 모든 함수처럼 호출 쿼리가 AS 절로 레코드 구조를 명시해야 해요.) 입력 레코드 값이 없으므로 일치하지 않는 컬럼은 항상 널로 채워져요. 예: json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype) → (1,[1,2,3],{1,2,3},,(123,"a b c"))
json_to_recordset ( json ) → setof record / jsonb_to_recordset (...) → setof record — 최상위 객체 배열을 AS 절 복합 타입의 행 집합으로 펼쳐요. 각 요소는 json[b]_populate_record 처럼 처리돼요. 예: json_to_recordset('[{"a":1,"b":"foo"}, {"a":"2","c":"bar"}]') as x(a int, b text) → (1,foo), (2,)

수정·조작

jsonb_set ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean ] ) → jsonb — path 가 지정한 항목을 new_value 로 교체하거나, create_if_missing 이 true(기본값)이고 그 항목이 없으면 추가한 target 을 반환해요. 경로의 앞선 단계는 모두 존재해야 하고, 아니면 target 을 그대로 반환해요. 경로 지향 연산자처럼 경로의 음수 정수는 배열 끝에서 세요. 마지막 경로 단계가 범위 밖의 배열 인덱스이고 create_if_missing 이 true면, 인덱스가 음수면 배열 앞에, 양수면 배열 끝에 추가해요. 예: jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', '[2,3,4]', false) → [{"f1": [2, 3, 4], "f2": null}, 2, null, 3]
jsonb_set_lax ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean [, null_value_treatment text ]] ) → jsonb — new_value 가 NULL이 아니면 jsonb_set 과 동일해요. NULL이면 null_value_treatment 값에 따라 동작하는데, 그 값은 'raise_exception', 'use_json_null', 'delete_key', 'return_target' 중 하나여야 하고 기본은 'use_json_null' 이에요. 예: jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}', null, true, 'return_target') → [{"f1": 99, "f2": null}, 2]
jsonb_insert ( target jsonb, path text[], new_value jsonb [, insert_after boolean ] ) → jsonb — new_value 를 삽입한 target 을 반환해요. path 가 배열 요소를 가리키면 insert_after 가 false(기본)면 그 앞에, true면 뒤에 삽입해요. 객체 필드를 가리키면 객체가 그 키를 이미 갖고 있지 않을 때만 삽입해요. 앞선 경로 단계는 모두 존재해야 하고 아니면 그대로 반환. 마지막 경로 단계가 범위 밖 배열 인덱스면 음수는 앞, 양수는 뒤에 추가. 예: jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"') → {"a": [0, "new_value", 1, 2]}
json_strip_nulls ( target json [, strip_in_arrays boolean ] ) → json / jsonb_strip_nulls (...) → jsonb — 주어진 JSON 값에서 null 값인 모든 객체 필드를 재귀적으로 삭제해요. strip_in_arrays 가 true(기본은 false)면 null 배열 요소도 삭제하고, 아니면 그대로 두고, 맨몸 null 값은 절대 삭제하지 않아요. 예: json_strip_nulls('[{"f1":1, "f2":null}, 2, null, 3]') → [{"f1":1},2,null,3]
jsonb_pretty ( jsonb ) → text — 주어진 JSON 값을 들여쓰기된 보기 좋은 텍스트로 변환해요. 예: jsonb_pretty('[{"f1":1,"f2":null}, 2]') → 줄바꿈·들여쓰기된 배열.

타입·크기

json_typeof ( json ) → text / jsonb_typeof (...) → text — 최상위 JSON 값의 타입을 텍스트로 반환해요. 가능한 타입은 object, array, string, number, boolean, null 이에요. (null 결과는 SQL NULL과 혼동하면 안 돼요.) 예: json_typeof('-123.4') → number, json_typeof('null'::json) → null, json_typeof(NULL::json) IS NULL → t

jsonpath 처리 함수

jsonb_path_exists ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean — JSON 경로가 지정 JSON 값에 대해 어떤 항목이라도 반환하는지 확인해요. (SQL 표준 JSON 경로 표현에만 유용.) vars 인자가 주어지면 JSON 객체여야 하고, 그 필드가 jsonpath 표현에 치환될 이름 붙은 값을 제공해요. silent 가 true면 @?·@@ 연산자와 같은 오류를 억제해요. 예: jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') → t
jsonb_path_match ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean — 지정 JSON 값에 대한 JSON 경로 조건 검사의 SQL 불리언 결과를 반환해요. (경로 결과가 단일 불리언이 아니면 실패하거나 NULL 을 반환하므로 조건 검사 표현에만 유용.) 예: jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2, "max":4}') → t
jsonb_path_query ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb — JSON 경로가 지정 JSON 값에 대해 반환하는 모든 JSON 항목을 반환해요. SQL 표준 경로 표현이면 target 에서 선택된 값, 조건 검사 표현이면 true/false/null 이에요. 예: jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') → 2, 3, 4
jsonb_path_query_array ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb — 모든 항목을 JSON 배열로 반환해요. 예: jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') → [2, 3, 4]
jsonb_path_query_first ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb — 첫 항목(결과 없으면 NULL)을 반환해요. 예: ... → 2
jsonb_path_exists_tz / jsonb_path_match_tz / jsonb_path_query_tz / jsonb_path_query_array_tz / jsonb_path_query_first_tz — 시간대 인식 변환이 필요한 날짜/시간 값 비교를 지원하는 점만 제외하면 _tz 없는 대응 함수와 같아요. 예를 들어 날짜만 있는 값 2015-08-02 를 시간대 있는 타임스탬프로 해석해야 하므로 결과가 현재 TimeZone 설정에 따라 달라져요. 이 의존성 때문에 이 함수들은 stable로 표시되어 인덱스에 쓸 수 없어요. _tz 없는 대응 함수는 immutable이라 인덱스에 쓸 수 있지만, 그런 비교를 요청하면 오류를 내요.

SQL/JSON 경로 언어

SQL/JSON 경로 표현은 JSON 값에서 항목을 지정하기 위해 쓰이며, XML 콘텐츠 접근에 쓰는 XPath와 비슷해요. PostgreSQL에서는 jsonpath 데이터 타입으로 구현돼요. JSON 쿼리 함수·연산자는 주어진 경로 표현을 경로 엔진에 넘겨 평가하고, 일치하면 해당 JSON 항목(들)을, 아니면 함수에 따라 NULL·false·오류를 반환해요.

경로 표현은 jsonpath 타입이 허용하는 요소들의 시퀀스로 이뤄지고, 보통 왼쪽에서 오른쪽으로 평가되며 괄호로 우선순위를 바꿀 수 있어요. 쿼리되는 JSON 값(컨텍스트 항목)을 가리키려면 $ 변수를 쓰고, 경로의 첫 요소는 항상 $ 여야 해요. 그 뒤에 접근자 연산자를 이어 붙여 JSON 구조를 단계별로 내려가 하위 항목을 검색해요.

예를 들어 GPS 추적기의 JSON 데이터가 있다고 해요.

SELECT '{
  "track": {
    "segments": [
      { "location": [ 47.763, 13.4034 ], "start time": "2018-10-14 10:05:14", "HR": 73 },
      { "location": [ 47.706, 13.2635 ], "start time": "2018-10-14 10:39:21", "HR": 135 }
    ]
  }
}' AS json \gset

추적 세그먼트를 가져오려면 .key 접근자로 객체를 내려가요: jsonb_path_query(:'json', '$.track.segments')
배열 내용을 가져오려면 [*] 를 써요: jsonb_path_query(:'json', '$.track.segments[*].location') → [47.763, 13.4034], [47.706, 13.2635]
첫 세그먼트만: [0] 첨자를 쓰고 배열 인덱스는 0부터예요: jsonb_path_query(:'json', '$.track.segments[0].location')
메서드를 쓰려면 점 앞에 메서드 이름을 붙여요: jsonb_path_query(:'json', '$.track.segments.size()') → 2

필터 표현식

경로에는 SQL의 WHERE 절처럼 동작하는 필터 표현식을 넣을 수 있어요. 필터는 물음표로 시작해 괄호 안에 조건을 담아요: ? (condition). 필터는 적용할 경로 평가 단계 바로 뒤에 써야 하고, 조건을 만족하는 항목만 남도록 그 단계 결과를 걸러내요. SQL/JSON은 삼값 논리를 정의해 조건이 true/false/unknown 을 낼 수 있고, unknown 은 SQL NULL 처럼 is unknown 술어로 검사할 수 있어요.

@ 변수는 필터 안에서 고려 중인 값(앞 경로 단계의 한 결과)을 나타내요. 예:

-- 심박수 130 초과인 모든 값
SELECT jsonb_path_query(:'json', '$.track.segments[*].HR ? (@ > 130)');   -- 135
-- 조건을 만족하는 세그먼트의 시작 시간 (필터를 앞 단계에 적용)
SELECT jsonb_path_query(:'json', '$.track.segments[*] ? (@.HR > 130)."start time"');  -- "2018-10-14 10:39:21"
-- 필터 여러 개를 연속으로
SELECT jsonb_path_query(:'json', '$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"');

SQL 표준과의 차이

PostgreSQL의 SQL/JSON 경로 언어 구현은 두 가지 차이가 있어요. 첫째, 불리언 조건 검사 표현식: SQL 표준 확장으로 PostgreSQL 경로 표현은 불리언 술어가 될 수 있어요. 표준 경로 표현은 쿼리되는 값의 관련 요소를 반환하지만, 조건 검사 표현은 술어의 단일 삼값 jsonb 결과(true/false/null)를 반환해요. 조건 검사 표현은 @@ 연산자(와 jsonb_path_match 함수)에 필요하고, @? 연산자(와 jsonb_path_exists 함수)에는 쓰면 안 돼요. 둘째, like_regex 필터의 정규식 해석에 사소한 차이가 있어요.

strict 모드와 lax 모드

JSON을 쿼리할 때 경로 표현이 실제 JSON 구조와 안 맞을 수 있는데, 존재하지 않는 객체 멤버나 배열 요소에 접근하려는 것은 구조 오류(structural error)로 정의돼요. SQL/JSON 경로 표현은 두 가지 처리 모드가 있어요.

lax(기본) — 경로 엔진이 쿼리된 데이터를 지정 경로에 암시적으로 적응시켜요. 아래에서 설명하는 방식으로 고칠 수 없는 구조 오류는 억제되어 일치가 없어져요.
strict — 구조 오류가 발생하면 오류를 냅니다.

lax 모드는 JSON 데이터가 기대 스키마에 안 맞을 때 문서와 경로 표현의 매칭을 돕는데, 연산 요구에 안 맞는 피연산자는 자동으로 SQL/JSON 배열로 감싸거나, 요소를 시퀀스로 풀어낸 뒤 연산을 수행해요. 또 비교 연산자가 lax 모드에서 자동으로 피연산자를 풀어내 배열을 바로 비교할 수 있어요. 크기 1 배열은 그 단일 요소와 같다고 봐요. 단, 경로 표현이 type() 이나 size() 메서드를 포함하거나, 쿼리된 데이터에 중첩 배열이 있을 때는 자동 풀기를 하지 않아요(그 경우 최바깥 배열만 풀리므로 암시적 풀기는 각 경로 평가 단계에서 한 단계만 내려갈 수 있어요).

lax 모드의 풀기 동작은 놀라운 결과를 낼 수 있어요. 예를 들어 .$.**.HR 은 .** 접근자가 segments 배열과 그 각 요소를 모두 선택하는데 .HR 접근자가 lax 모드에서 배열을 자동 풀기 때문에 HR 값을 두 번씩 선택해요. 이런 놀라운 결과를 피하려면 .** 접근자는 strict 모드에서만 쓰는 걸 권장해요.

jsonpath 연산자와 메서드

단항 연산자와 메서드는 앞 경로 단계의 여러 값에 적용할 수 있지만, 이항 연산자(덧셈 등)는 단일 값에만 적용돼요. lax 모드에서 배열에 적용한 메서드는 배열의 각 값에 대해 실행되는데, .type() 과 .size() 는 예외로 배열 자체에 적용돼요.

주요 연산자·메서드 (예시와 함께):

산술: number + number, + number(단항), number - number, - number(부정), number * number, number / number, number % number(나머지). 예: jsonb_path_query('[2]', '$[0] + 3') → 5
.type() — JSON 항목의 타입. jsonb_path_query_array('[1, "2", {}]', '$[*].type()') → ["number", "string", "object"]
.size() — 배열 요소 수(배열이 아니면 1). jsonb_path_query('{"m": [11, 15]}', '$.m.size()') → 2
.boolean() — JSON 불리언·숫자·문자열에서 변환. .string() — 불리언·숫자·문자열·날짜시간에서 변환. .double() — 근사 부동소수점. .bigint() — 큰 정수. .decimal([precision[, scale]]) — 반올림 소수. .integer(), .number() — 각 정수·숫자 변환. 예: jsonb_path_query('{"len": "1.9"}', '$.len.double() * 2') → 3.8
.ceiling(), .floor(), .abs() — 각 올림, 내림, 절댓값. 예: jsonb_path_query('{"z": -0.3}', '$.z.abs()') → 0.3
날짜/시간: .datetime() / .datetime(template)(ISO 형식 순차 매칭, to_timestamp 와 같은 파싱 규칙·세 가지 예외), .date(), .time() / .time(precision), .time_tz() / .time_tz(precision), .timestamp() / .timestamp(precision), .timestamp_tz() / .timestamp_tz(precision). 예: jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(2)') → "2023-08-15T12:34:56.79"
.keyvalue() — 객체의 키/값 쌍을 "key", "value", "id" 세 필드를 가진 객체의 배열로. "id" 는 쌍이 속한 객체의 고유 식별자. 예: jsonb_path_query_array('{"x": "20", "y": 32}', '$.keyvalue()') → [{"id": 0, "key": "x", "value": "20"}, {"id": 0, "key": "y", "value": 32}]

참고: datetime() 과 datetime(template) 의 결과 타입은 date, timetz, time, timestamptz, timestamp 가 될 수 있고 동적으로 정해져요. datetime() 은 입력 문자열을 date, timetz, time, timestamptz, timestamp 의 ISO 형식에 순차적으로 매칭하다 첫 일치에서 멈춰 해당 타입을 내고, datetime(template) 은 제공된 템플릿 문자열의 필드에 따라 결정돼요. 두 메서드 모두 to_timestamp SQL 함수와 같은 파싱 규칙을 쓰되 세 가지 예외가 있어요: 불일치 템플릿 패턴을 허용하지 않고, 템플릿 문자열에 마이너스·마침표·슬래시·쉼표·아포스트로피·세미콜론·콜론·공백만 구분자로 허용하며, 템플릿의 구분자가 입력 문자열과 정확히 일치해야 해요.

JSON 함수와 연산자