개이득인개발
4k
2021-05-21 11:55:55 작성 2021-05-21 11:56:23 수정됨
1
134

postgresql json 데이터 select시 value 값을 변경하는 방법?


예를 들어

a 테이블에 아래와 같은 데이터(jsnob배열)를 가지는 컬럼이 있습니다.

                                         data

[ {key: 'food', value: 'waterMelon'}, {key: 'food', value: 'ramen'} ]


b 테이블의 컬럼은 a테이블 value값의 한글 이름을 가집니다.

       code              name

  waterMelon         수박

       ramen            라면


a테이블을 질의해서 json의 value값을 b 테이블의 code와 같은 key의 value를 b테이블의 name로 변경하고싶은데, json데이터의 db경험이 없어 잘 떠오르지가 않네요.. 조언 부탁드립니다. 

0
  • 답변 1

  • 행복한나날을
    89
    2021-05-21 17:35:34 작성 2021-05-21 17:44:54 수정됨

    안녕하세요? 저도 JSON 데이터를 다뤄본 적은 없지만 감히 시간을 내어 도전해 봤습니다.

    저는 두 가지 방법으로 접근해보았습니다.
    첫 번째는 Raw Data를 제가 생각한 데이터 형태로 구성했을 때, 두 번째는 질문자님께서 표현하신 데이터 형태일 때 구현해야 할 방법을 각각 생각해 보았습니다.

    첫 번째는 굳이 key와 value라는 형태보다 아래와 같이 key 자리에 food를, value 자리에 해당 음식명의 형태로 가정하여 접근하였습니다.

    참고로, PostgreSQL 에서는 column type을 JSON 으로 지정할 수 있습니다. 대신, insert 할 때 JSON 형태의 문자열 전체를 작은 따옴표로 감싸고, 각각의 내용은 큰 따옴표로 감싸야 합니다.

    출처: [DB] postgreSQL json - insert 하기


    그렇다면 이제부터 어떻게 두 테이블과 JOIN 할 것인가?
    뭐... 저는 단순하게 하나의 형태로 일치시켜야 된다고 생각했습니다. 단순한 테이블 형태의 표현으로 일치하고자 다음과 같이 접근했습니다.

    출처: Extract key, value from json objects in Postgres

    select json_each_text(data_food) from food_json;
    
    select d.key, d.value from food_json join json_each_text(food_json.data_food) d 
      on true order by key, value;

    이 두 쿼리문의 결과를 토대로 아래와 같은 쿼리문을 실행하면 JOIN이 되어 표현됩니다.


    with food_json_table as (select d.key, d.value from food_json join 
      json_each_text(food_json.data_food) d on true order by key, value)
    select a.key, b.code, b.name from food_json_table a join 
      (select * from food_table) b on a.value = b.code;



    그리고 두 번째는 아래와 같이 질문자님께서 표현하신 형태의 데이터가 있다고 생각하겠습니다.

    하나의 JSON Array 내 Object가 두 개가 있을 때, 저는 첫 번째 방법과 동일하게 각각을 분리하고자 하였습니다.

    출처: PostgreSQL에서 JSON Array 쿼리하기PostgreSQL: Documentatio: 10: 9.15. JSON Functions and Operators

    출처에서는 jsonb_array_elements(jsonb) 함수를 사용했는데, JSON 형태도 똑같습니다.

    select json_array_elements(data_food) from food_json;


    혹시, 첫 번째 방법에서 결과물을 일부러 보여드리지 않았는데, 혹시 실행해 보셨나요?
    두 번째 방법에서도 동일한 형태로 이어가고자 json_populate_recordset 라는 함수를 사용할 겁니다.

    출처: Postgresql json_populate_recordset/jsonb_populate_recordset 설명 : 포스트그레스큐엘 함수

    이 함수를 사용하기 전에, PostgreSQL: Documentation: 10: CREATE TYPE 에서 보면 알다시피, 사용자 정의 형식을 생성해야 합니다.

    create type foodset as (key text, value text);
    
    select json_populate_recordset(null::foodset, food_json.data_food) from food_json;
    
    select key, value from json_populate_recordset(null::foodset, 
      (select data_food from food_json));

    각각의 실행 결과는 아래 그림과 같습니다.


    이제 이렇게 하면 첫 번째 방법의 마지막에 보여드린 쿼리문과 동일한 형태로 JOIN 하게 되면 결과는 다음과 같이 나오게 됩니다.

    with food_json_table as (select key, value from 
      json_populate_recordset(null::foodset, (select data_food from food_json)))
    select a.key, b.code, b.name from food_json_table a join 
      (select * from food_table) b on a.value = b.code;



    혹시... 원래 JSON 형태로 보고 싶으신가요? json_agg 함수와 row_to_json 함수를 조합하면 JSON 형태로 볼 수 있습니다.

    출처: PostgreSQL: Documentation: 10: 9.20. Aggregate FunctionsPostgreSQL row형태의 자료를 JSON 형태로 반환

    with food_json_table as (select key, value from 
      json_populate_recordset(null::foodset, (select data_food from food_json))) 
    select json_agg(row_to_json(c)) as food_json_convert from 
    (select a.key, b.code, b.name as value from food_json_table a join 
      (select * from food_table) b on a.value = b.code) c;



    원하는 답이 되었는지 모르겠습니다만... PostgreSQL에 대해 오늘도 배워 가는 시간이었습니다.
    이상입니다. 감사합니다.

  • 로그인을 하시면 답변을 등록할 수 있습니다.