raw query a json column in postgresql

한 Entity의 프로퍼티에 접근해야 하는데 타입이 json이었다. raw query로 json column에 접근하는 법을 알아보자.

config

{
  "pricing": {
    "worker": {
      "job": {
        "currency": string,
        "unitPrice": number
      },
      "polygon": [
        {
          "currency": string,
          "unitPrice": number,
          "pricingType": string
        }
      ]
    },
    "reviewer": {
      "job": {
        "currency": string,
        "unitPrice": number
      }
    }
  },
    ...
}

JSON Functions and Operators

1. pricing 접근

What is the difference between ->> and -> in Postgres SQL?

::JSONB 사용 X

const keyOfJsonTest = await this.subtasksRepository
      .createQueryBuilder('subtask')
      .select(`(subtask.config->'pricing')`)
      .where('subtask.id = :subtaskId', { subtaskId })
      .getRawMany();

console.log('json result====', keyOfJsonTest);
/* 
{
  '?column?': {
    worker: { job: [Object], polygon: [Array] },
    reviewer: { job: [Object] }
  }
}
*/

::JSONB 사용

const keyOfJsonTest = await this.subtasksRepository
    .createQueryBuilder('subtask')
    .select(`(subtask.config->'pricing')::JSONB`)
    .where('subtask.id = :subtaskId', { subtaskId })
    .getRawMany();

console.log('json result====', keyOfJsonTest);

/* 
{
  jsonb: {
    worker: { job: [Object], polygon: [Array] },
    reviewer: { job: [Object] }
  }
}
*/

2. worker 접근

const keyOfJsonTest = await this.subtasksRepository
      .createQueryBuilder('subtask')
      .select(`((subtask.config->'pricing')->'worker')::JSONB`)
      .where('subtask.id = :subtaskId', { subtaskId })
      .getRawOne();

console.log('json result====', keyOfJsonTest);

/* 
{
  jsonb: { job: { currency: 'KRW', unitPrice: 5 }, polygon: [ [Object] ] }
}
*/

3. unitPrice 접근

const keyOfJsonTest = await this.subtasksRepository
      .createQueryBuilder('subtask')
      .select(
        `((((subtask.config->'pricing')->'worker')->'job')->'unitPrice')`,
        'unitPrice',
      )
      .where('subtask.id = :subtaskId', { subtaskId })
      .getRawOne();

console.log('json result====', keyOfJsonTest);

/* 
 { unitPrice: 5 }
*/

최종 값인 unitPrice 하나만 가져오고 type이 number라서 JSONB를 쓸 필요가 없어졌다. 값을 잘 가져오고 있다 🤩!