[PostgreSQL] jsonpath 필터 — JSONB 내부에서 부등호·범위·정규식으로 검색한다

설치·접속: PostgreSQL 설치와 접속

부제: 이벤트 로그 JSONB payload에서 items 중 amount가 100 이상인 항목만 뽑아야 하는데, @> 포함 연산으로는 등호밖에 안 돼서 못 할 때

@> 는 "이 값을 포함하느냐"는 등호 매칭만 된다. 부등호·범위·정규식은 SQL/JSON path로 간다. @? 는 경로 조건을 만족하는 요소가 하나라도 있는지 판정하고, jsonb_path_query 는 조건에 맞는 요소만 집합으로 꺼낸다. $min/$max 변수 바인딩까지 된다.

CREATE TABLE events (id int primary key, payload jsonb);
INSERT INTO events VALUES
 (1, '{"user":"alice","items":[{"sku":"A1","amount":40},{"sku":"A2","amount":120}]}'),
 (2, '{"user":"bob","items":[{"sku":"B1","amount":30},{"sku":"B2","amount":55}]}'),
 (3, '{"user":"carol","items":[{"sku":"C1","amount":200}]}'),
 (4, '{"user":"dave","items":[{"sku":"D1","amount":15},{"sku":"D2","amount":15}]}');

-- amount가 100 이상인 item이 하나라도 있는 이벤트
SELECT id, payload->>'user' AS "user"
FROM events
WHERE payload @? '$.items[*].amount ? (@ >= 100)';

 id | user
----+-------
  1 | alice
  3 | carol
(2 rows)

$.items[*].amount 로 배열 안 모든 amount를 훑고, ? (@ >= 100) 필터로 100 이상만 남긴다. 하나라도 있으면 @? 가 true라 그 행이 선택된다. alice(120)와 carol(200)이 걸리고, bob·dave는 전부 100 미만이라 빠졌다. @> 로는 "amount가 정확히 X" 밖에 표현 못 하니, 범위 조건은 이 방식이 유일하다. jsonpath 표현식은 GIN(jsonb_path_ops) 인덱스와도 맞물려 대량 데이터에서도 쓸 수 있다.

이렇게도 쓴다

jsonb_path_query로 조건에 맞는 요소만 꺼낸다. $min/$max 변수 바인딩으로 파라미터화. (조합: 변수 바인딩)

SELECT id,
  jsonb_path_query(payload, '$.items[*] ? (@.amount >= $min && @.amount <= $max)',
                   '{"min":30,"max":60}') AS item
FROM events ORDER BY id;

 id |            item
----+-----------------------------
  1 | {"sku": "A1", "amount": 40}
  2 | {"sku": "B1", "amount": 30}
  2 | {"sku": "B2", "amount": 55}
(3 rows)

 

like_regex로 JSON 내부 문자열을 정규식 매칭한다. flag "i"는 대소문자 무시.

SELECT jsonb_path_query('["A1","A2","B10","c9"]',
  '$[*] ? (@ like_regex "^a" flag "i")');   -- "A1", "A2"

 

@> 등호 매칭과 대조: 정확히 그 값을 포함하는지만 본다. 범위는 표현 불가.

SELECT id FROM events WHERE payload @> '{"items":[{"amount":200}]}';  -- carol만

 

@@ 로 jsonpath 술어 전체의 참·거짓을 평가한다. WHERE 조건으로 바로 쓴다.

SELECT id FROM events WHERE payload @@ '$.items[*].amount > 100';

 

jsonb_path_query_array로 매칭 요소를 배열 하나로 모은다. 행 폭발 없이 집계.

SELECT id, jsonb_path_query_array(payload, '$.items[*] ? (@.amount >= 50)') FROM events;

 

exists()로 중첩 경로 존재 여부를 술어 안에서 판정한다.

SELECT jsonb_path_query('{"a":1}', 'strict $ ? (exists(@.a))');

 

jsonb_path_ops GIN 인덱스로 jsonpath 검색을 가속한다. (조합: GIN)

CREATE INDEX idx_events_path ON events USING gin (payload jsonb_path_ops);