PostgreSQL에서 JSON Type 컬럼 데이터 추출

예를 들어 회사원 테이블을 만든다면

 

CREATE TABLE office_tb (
id int not null,
office_person json,
CONSTRAINT office_tb _pkey PRIMARY KEY (id)
);

 

간단히 id, 입사날짜, 퇴사날짜 데이터 넣기

 

INSERT INTO office_tb  (id, office_person )
VALUES

(1,{"dataList":[{"person":"이순신","joinDate":"20211025","leaveDate":"20221028"}),

(2,{"dataList":[{"person":"안중근","joinDate":"20200123","leaveDate":"20230103"}),

(3,{"dataList":[{"person":"유관순","joinDate":"20220907","leaveDate":"20230812"}).

(4,{"dataList":[{"person":"홍길동","joinDate":"20221025","leaveDate":"20231026"});

 

SELECT
    (b->>'person')::text as person,
    (b->>'joinDate')::text as joinDate,

    (b->>' leaveDate ')::text as leaveDate ,
 FROM pro_quote_prdt_info as a
CROSS JOIN
    json_array_elements(a. office_tb  -> 'dataList') as b;