[PostgreSQL] jsonb_path_query (JSONPath) 로 중첩 JSON을 조건까지 걸어 질의하기

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

부제: 중첩된 jsonb에서 "specs.ram이 16 초과인 것", "배열 안에 조건 맞는 원소가 있는 것"처럼 화살표(->)로는 지저분해지는 조건부 탐색이 필요할 때

-- $.a.b 경로 문법. jsonb_path_query 는 매칭 값을 뽑는다
SELECT name, jsonb_path_query(attrs, '$.specs.ram') AS ram
FROM m4_products WHERE attrs @> '{"category":"laptop"}';
-- ? (@ ...) 는 필터. @ 는 "현재 값". jsonb_path_exists 로 WHERE
SELECT name FROM m4_products WHERE jsonb_path_exists(attrs, '$.specs.ram ? (@ > 16)');

    name    | ram 
------------+-----
 갤럭시북4  | 16
 그램16     | 32
 맥북프로14 | 18

    name    
------------
 그램16
 맥북프로14

JSONPath는 $(루트)에서 시작해 .키로 내려가고, ? (@ 조건)으로 값을 거른다(@가 현재 원소). -> 체이닝은 값을 꺼내기만 하지만 JSONPath는 경로 탐색 + 조건 판단을 한 식에 담는다. WHERE에 쓸 땐 boolean을 돌려주는 jsonb_path_exists() 또는 @@ 연산자를 쓴다. 함정: jsonb_path_query는 매칭 값을 여러 행으로 뱉는 set-returning 함수라 SELECT 목록에 그냥 넣으면 행이 늘어난다 — 배열로 받으려면 jsonb_path_query_array를 쓴다.

이렇게도 쓴다

@@ 술어로 WHERE 필터 — GIN 인덱스도 받는 형태.

SELECT name FROM m4_products WHERE attrs @@ '$.price > 1000000';

 

배열 원소 전부 순회 [*] 하고 배열로 받기.

SELECT name, jsonb_path_query_array(attrs, '$.tags[*]') AS tags
FROM m4_products WHERE attrs ? 'tags';

 

객체를 key/value로 펼쳐 조건 걸기 — 재고 있는 지점 이름만. (keyvalue())

SELECT name, jsonb_path_query_array(attrs, '$.stock.keyvalue() ? (@.value > 0).key')
FROM m4_products WHERE attrs ? 'stock';

 

문자열 정규식 매칭 — 브랜드가 "애"로 시작. (like_regex)

SELECT name FROM m4_products WHERE attrs @@ '$.brand like_regex "^애"';

 

없는 경로는 에러가 아니라 그냥 매칭 0건.

SELECT name FROM m4_products WHERE jsonb_path_exists(attrs, '$.specs.gpu');  -- 0 rows

 

배열에서 조건 맞는 원소만 값으로 — set 반환이라 행이 여러 개.

SELECT jsonb_path_query('{"a":[1,2,3,4]}', '$.a[*] ? (@ >= 3)');  -- 3, 4