AI 학습용 데이터 품질 검사 당시 json 데이터를 다룰 기회가 있었다. 구축된 학습 데이터에 대해 얼마나 다양한 데이터를 이용했는지 확인하는 작업을 했고, 데이터는 최종적으로 json형식으로 전달받았다. 파이썬에서 json데이터를 불러와 작업하기 위해 PostgreSQL DB에 json 데이터를 text 타입으로 적재했고, 필요한 프로퍼티 값은 jsonb_path_query를 이용해 추출할 수 있었다. 이 글에서 jsonb_path_query를 어떻게 사용했는지 정리해보려고 한다.
- DB 환경 : PostgreSQL
- file_cn은 json데이터가 담겨있는 컬럼이다.
- file_cn::jsonb는 실제로는 텍스트 형식으로 insert 되어 있는 file_cn의 데이터를 jsonb 형식으로 변환(cast)하는 역할을 한다.
- json과 jsonb는 실제로 다른 타입이지만, 이 글에서는 jsonb 타입을 사용하겠다. json과 jsonb의 차이는 다른 글에서 다루도록 하겠다.
- jsonb_path_query는 postgresql에서 지원하는 기능이다.
1. 개요
jsonb_path_query는 jsonb형식의 데이터에서 필요한 프로퍼티의 경로(path)를 지정해 해당 값을 Read할 수 있게 도와준다. 기본적인 사용법은 아래와 같다.
select jsonb_path_query(①jsonb 타입의 값을 가지는 컬럼, ②프로퍼티 경로)
from 테이블① jsonb 타입의 값을 가지는 컬럼이 있다면 바로 그 컬럼명을 입력하면 된다. 하지만 jsonb 값이 text타입으로 존재한다면, "컬럼명::jsonb"을 입력해 text타입을 jsonb타입으로 변환해줄 수 있다.
② 프로퍼티 경로는 다음과 같은 규칙이 있다.
- 프로퍼티 경로는 작은 따옴표(' ')로 감싸져야 한다.
- 프로퍼티 경로는 $로 시작한다. $의 의미는 root object 또는 root array이다. 즉 jsonb 데이터의 시작점이다.
- object 내에 특정 프로퍼티 값에 접근하고 싶을 때는 .(온점)을 이용한다. ex) "object.property"
- array의 각 원소값에 접근하고 싶을 때는 [](대괄호)를 이용한다. [] 안에 정수를 입력해 원소 인덱스를 지정할 수도 있고, :(콜론)과 정수를 이용해 slicing할 수도 있다. 또한 [*]를 이용하면 모든 원소에 접근한다. ex) "array[2]", "array[:2]", "array[*]"
- root가 object타입이고 root안의 프로퍼티 값에 접근하려면 '$.'을 이용하고, root가 array타입이고 root 안의 원소 값에 접근하려면 '$[*]'을 이용한다.
- array의 원소값이 object형식이고 object내의 특정 프로퍼티로 접근하고 싶다면 [](대괄호) 다음에 바로 .(온점)을 이용한다. ex) "array[2].property", "array[*].property", "$[*].property"
※ '\u0000' 처리
text 타입을 jsonb타입으로 변환할 때, 주의해야 할 점이 있다.이건 python에서 sqlalchemy를 통해 query statment를 사용할 때만 제기되는 문제일 수도 있다. 하지만 로직 상 python에서 query를 날린다고 해서 작업은 DB에서 처리된 다음 python으로 export되는 것이기 때문에 DB자체의 이슈일 수도 있다.
바로 '\u0000'의 처리 문제이다. \u0000은 ASCII 문자집합에서 null character에 해당하는 문자다.(ASCII 문자셋은 \u0000부터 \u007f까지 대응되는 일부분이다.) text타입에서는 눈에 보이진 않지만 \u0000으로 표현되는 부분이 있고, 이 때문에 jsonb로 변환해서 jsonb_path_query를 이용해서 데이터를 read할 때 에러가 발행할 수 있다. 따라서 안전하게 데이터를 read하기 위해서는 replace(컬럼, '\\u0000', '')과 같이 \u0000을 다른 문자로 대체하는 작업이 선행되어야 한다. (\\u0000에서 \를 두번 사용한 이유는 이스케이프 처리를 하기 위함이다.)
※ 프로퍼티 경로는 filtering을 비롯해 다양한 문법을 적용할 수 있다. 해당 사항은 다음 사이트를 참고하자.
https://www.postgresql.org/docs/12/functions-json.html
9.15. JSON Functions and Operators
9.15. JSON Functions and Operators 9.15.1. Processing and Creating JSON Data 9.15.2. The SQL/JSON Path Language This section describes: functions and …
www.postgresql.org
2. jsonb 데이터를 통째로 가져오기
jsonb를 통째로 추출할 때는, json 데이터 컬럼을 select한다.
예시로 사용할 테이블(target_table)이 다음과 같이 생겼다고 하자.
| id | file_nm | file_cn | |
| 1 | file1.json | {"property1" : "value11", "property2" : "value21"} | |
| 2 | file2.json | {"property1" : "value12", "property2" : "value22"} | |
| 3 | file2.json | {"property1" : "value13", "property2" : "value23"} | |
| 4 | file2.json | {"property1" : "value14", "property2" : "value24"} | |
| ... | ... | ... | |
일반적은 select 문을 이용하여 file_cn 컬럼의 데이터를 통째로 뽑았기 때문에 결과는 바로 아래 표와 같이 나온다.
select file_cn::jsonb as jsonb
from target_table| jsonb |
| {"property1" : "value11", "property2" : ["value21", "value22"]} |
| {"property1" : "value12", "property2" : ["value23", "value24"]} |
| {"property1" : "value13", "property2" : ["value25", "value26"]} |
| {"property1" : "value14", "property2" : ["value27", "value28"]} |
| ... |
3. jsonb 데이터의 특정 프로퍼티 값을 가져오기
특정 프로퍼티의 값만을 추출해보자.
# 1. 모든 property1값을 추출
select jsonb_path_query(file_cn::jsonb, '$.property1') as target_value1
from target_table
# 2. 모든 property2값을 추출
select jsonb_path_query(file_cn::jsonb, '$.property2') as target_value2
from target_table
# 3. 모든 property2의 원소 값을 추출
select jsonb_path_query(file_cn::jsonb, '$.property2[*]') as target_value3
from target_table| index | target_value1 | index | target_value2 | index | target_value3 |
| 1 | value11 | 1 |
["value21", "value22"] |
1 | value21 |
| 2 | value22 | ||||
| 2 | value12 | 2 |
["value21", "value22"] |
3 | value23 |
| 4 | value24 | ||||
| 3 | value13 | 3 |
["value21", "value22"] |
5 | value25 |
| 6 | value26 | ||||
| 4 | value14 | 4 |
["value21", "value22"] |
7 | value27 |
| 8 | value28 | ||||
| ... | ... | ... | ... | ... |
4. 테이블의 한 행과 json의 프로퍼티 값이 설명하는 대상
하나의 프로퍼티 값을 가져올 때처럼 두 개의 프로퍼티값을 가져올 것은 단순하지 않다. query의 결과표에서 한 행은 공통적인 것에 대해 설명하고 있어야 한다.
아래는 A4, A3, B4 등의 용지 사이즈에 관한 데이터 테이블(표)이다. 이 테이블에서 첫 번째 행은 A4용지, 두 번째 행은 A3용지에 대한 데이터이다. 그런데 세번째 행의 첫번째 열 값은 A6, 두번째 열 값은 A2용지에 대한 데이터라고 한다면, 굉장히 이질감이 들것이다. 특수한 예외도 있겠지만, 일반적으로 테이블의 한 행은 하나의 공통적인 대상에 대한 데이터이기 때문이다.
| height | width |
| 297 | 210 |
| 420 | 297 |
| 105 | 420 |
jsonb_path_query를 통해 추출한 테이블의 행도 하나의 대상을 설명해야 한다. 본격적인 설명 이전에 일반적인 테이블과 json 타입의 컬럼이 포함된 테이블이 어떻게 다른지 알면 jsonb_path_query에 대한 이해가 쉬울 것 같다.
일반적인 테이블은 하나의 컬럼에 대해 개별 행들이 어떤 대상을 설명하는 하나의 값이다. 하지만 json타입의 값에는 다양한 프로퍼티가 포함될 수 있다. 즉, 하나의 행은 큰 틀에서 하나의 대상을 설명하지만, 세부적으로는 여러 대상을 설명하는 다양한 값을 가질 수 있다는 말이 된다. 더 나아가 프로퍼티 값이 array(배열) 형식으로 되어 있으면, array값은 여러 대상을 설명할 수도 있게 된다.(개별 원소들이 각기 다른 대상을 설명) 다음과 같이 예를 들 수 있겠다.
위의 이미지에서 동물들에 대한 정보를 담은 테이블을 만든다고 했을때, json타입을 사용하지 않는 경우와 json타입을 사용하는 경우가 아래와 같이 나뉠 수 있다.
| file_name | animal_id | animal |
| zootopia.jpg | 1 | 토끼 |
| zootopia.jpg | 2 | 개 |
| zootopia.jpg | 3 | 여우 |
| zootopia.jpg | 4 | 표범 |
| zootopia.jpg | 5 | 코끼리 |
| zootopia.jpg | 6 | 돼지 |
| zootopia.jpg | 7 | 사자 |
| image_id | animal_json |
| 1 | {"file_name" : "zootopia.jpg", "animals" : [ "토끼", "개", "여우", "표범", "코끼리", "돼지", "사자" ]} |
첫번째 테이블은 한 행이 한마리의 동물을 설명하고 있고, 두번째 테이블은 한 행이 하나의 이미지를 설명하고 있다. animal_json에서 하나의 json은 하나의 이미지를 설명하지만 그 프로퍼티인 animals(array 타입)의 개별 원소는 개별 동물들에 대한 설명이다. 즉, 큰틀에서는 이미지를 설명하지만, 세부적으로는 동물을 대상으로 설명하는 값도 있다는 것이다.
다시 말하자면, json에서는 array값을 통해 설명의 대상이 바뀔 수 있다. 그렇다면 array값을 가지는 프로퍼티가 여러 개 있다면 각각의 array의 원소값이 설명하는 대상도 여러 개일까? 그럴 수 있기 때문에 json의 array값을 유의해야 봐야 하는 것이다. 만약 위의 주토피아 이미지에서 육식 동물과 초식 동물을 나누어 annotation한 데이터가 아래와 같다고 하자.
| image_id | animals_json |
| 1 | {"file_name" : "zootopia.jpg", "categories" : [ { "id" : "1", "value" : "육식" }, { "id" : "2", "value" : "초식" }, { "id" : "3", "value" : "잡식", } ], "animals" : [ { "id" : "1", "name" : "토끼", "category_id" : "1" }, { "id" : "2", "name" : "개", "category_id" : "3" }, ... { "id" : "7", "name" : "사자", "category_id" : "2" ]} |
animals_json의 구조를 살펴보면 categories와 animals 프로퍼티가 array타입의 값을 가지고 있다. 두 프로퍼티 모두 zootopia.jpg를 설명하지만 categories의 개별 원소는 식성, animals의 개별 원소는 동물 객체를 대상으로 설명하고 있다.
정리하자면, query의 결과 테이블에서 하나의 행은 하나의 대상을 설명해야 한다. 하지만 json에서 array의 원소 값이 설명하는 대상은 json의 다른 프로퍼티가 설명하는 대상과 다를 수 있기 때문에 결과 테이블의 한 행이 여러 대상을 설명하지 않게끔 고려하여 query를 작성해야 한다.
5. 두 프로퍼티의 값을 가져오기
실제로 jsonb_path_query를 이용해 두 프로퍼티의 값을 가져와보자. array타입이 아닌 두 프로퍼티부터 하나만 array 타입인 두 프로퍼티, 모두 array 타입인 두 프로퍼티로 case를 나누어 살펴보는 것이 좋겠다.
1. array타입이 아닌 두 프로퍼티
이 경우에는 크게 고민할 필요 없이 jsonb_path_query를 원하는 프로퍼티에 각각 적용해 select하면 된다. 왜냐하면 array 타입이 아닌 프로퍼티가 설명하는 대상은 기존 테이블의 한 행이 설명하는 대상과 같을 것이기 때문이다.
만약 두 개의 프로퍼티가 다음과 같이 root object 혹은 root array 아래에서 병렬적으로 존재할 경우에는 별 문제가 되지 않는다.
# 첫 번째 파일
{"file_name" : "image1.jpg",
"Info" :{
"gender" : "남",
"age" : "18"
}
# 두 번째 파일
{"file_name" : "image1.jpg",
"Info" :{
"gender" : "여",
"age" : "26"
}
# 세 번째 파일
{"file_name" : "image2.jpg",
"Info" :{
"gender" : "여",
"age" : "43"
}
# 네 번째 파일
{"file_name" : "image2.jpg",
"Info" :{
"gender" : "남",
"age" : "32"
}이 때는 file_name와 gender 프로퍼티를 동시에 뽑는다고 해도 딱히 고려해야할 점이 없다.
select
jsonb_path_query(file_cn::jsonb, '$.file_name') as file_name,
jsonb_path_query(file_cn::jsonb, '$.Info.gender') as gender
from
target_table| file_name | gender |
| image1.jpg | 남 |
| image1.jpg | 여 |
| image2.jpg | 여 |
| image2.jpg | 남 |
하지만 json 내에 file_name은 하나지만, gender와 age는 두개씩 존재하는 경우에는 한층 더 복잡해진다. 아래처럼 병렬적이지 않은 file_name 프로퍼티와 gender, age 프로퍼티를 같이 뽑을 때는 다음과 같은 사항을 고려해야 한다.
1. 이 경우 결과 테이블의 row를 file_name을 기준으로 할 것인지, gender, age를 기준으로 할 것인지 선택해야 한다.
# 첫 번째 json
{"file_name" : "image1.jpg",
"Info" :[{"gender" : "남",
"age" : "18"},
{"gender" : "여",
"age" : "26"}]}
# 두 번째 json
{"file_name" : "image2.jpg",
"Info" :[{"gender" : "여",
"age" : "43"},
{"gender" : "남",
"age" : "32"}]}
- file_name을 기준으로 row를 뽑을 경우(결과 테이블을 아래처럼 생각하고 있다면) (확인)
| file_name | gender |
| image1.jpg | [남, 여] |
| image2.jpg | [여, 남] |
이 때는 jsonb_path_query를 다음과 같이 작성한다.
select
jsonb_path_query(file_cn::jsonb, '$.file_name') as file_name,
jsonb_path_query(file_cn::jsonb, '$.Info[*].gender') as gender
from
target_table
아래는 어레이를 파싱해서 뽑고, 동일한 레벨의 다른 프로퍼티를 뽑는 경우 (확인)
select
jsonb_path_query(file_cn::jsonb, '$.Info[*].gender') as gender,
jsonb_path_query(file_cn::jsonb, '$.file_name') as file_name
from
target_table
아래는 뭐가 다른 걸까? (확인)
select
jsonb_path_query(file_cn::jsonb, '$.file_name') as file_name,
jsonb_path_query(jsonb_path_query(file_cn::jsonb, '$')::jsonb, '$.Info[*].gender') as gender
from
target_table
json 데이터를 추출하는 방법은 jsonb_path_query 말고도 jsonb_path_array 등 다양한 방법이 있다. 이번 글에서는 jsonb_path_query를 이용해 특정 프로퍼티 값을 추출하는 방법을 기록했다.
'Language > Python' 카테고리의 다른 글
| Pytorch 디버그 모음 (0) | 2022.02.24 |
|---|
