[Postgresql] 데이터를 JSON 형태로 추출하는 방법

Postgresql의 json_build_object, json_object_agg 함수를 사용하면 데이터를 JSON 형태로 추출 할 수 있다. 해당 함수를 사용하여 Postgresql DB 정보를 추출하는 방법을 알아보자.

PostgreSQL Database 정보를 JSON 형태로 추출하는 SQL 스크립트

SELECT json_build_object(
    'database_name', current_database(),
    'postgresql_version', version(),
    'server_os', version(),
    'server_uptime', pg_postmaster_start_time() AT TIME ZONE 'UTC',
    'database_size', pg_size_pretty(pg_database_size(current_database())),
    'number_of_tables', (SELECT count(*) FROM information_schema.tables WHERE table_schema='public'),
    'number_of_indexes', (SELECT count(*) FROM pg_indexes WHERE schemaname='public'),
    'number_of_users', (SELECT count(*) FROM pg_user),
    'number_of_connections', (SELECT count(*) FROM pg_stat_activity),
    'database_parameters', (SELECT json_object_agg(name, setting) 
                             FROM pg_settings WHERE source IN ('default', 'configuration file'))
);

위의 SQL 쿼리는 PostgreSQL 데이터베이스 정보를 JSON 형식으로 반환한다. 이 쿼리를 실행하면 다음과 같은 JSON 형식의 결과가 반환된다.

json_build_object 함수를 사용한 Database 정보 추출 결과

{
   "database_name": "your_database_name",
   "postgresql_version": "PostgreSQL 13.3 on x86_64-apple-darwin19.6.0, compiled by Apple clang version 11.0.3 (clang-1103.0.32.62), 64-bit",
   "server_os": "PostgreSQL 13.3 on x86_64-apple-darwin19.6.0, compiled by Apple clang version 11.0.3 (clang-1103.0.32.62), 64-bit",
   "server_uptime": "2023-03-29T08:59:33.000Z",
   "database_size": "32 MB",
   "number_of_tables": 10,
   "number_of_indexes": 5,
   "number_of_users": 2,
   "number_of_connections": 1,
   "database_parameters": {
      "application_name": null,
      "client_encoding": "UTF8",
      "datestyle": "ISO, MDY",
      "default_text_search_config": "pg_catalog.english",
      "lc_messages": "en_US.UTF-8",
      "lc_monetary": "en_US.UTF-8",
      "lc_numeric": "en_US.UTF-8",
      "lc_time": "en_US.UTF-8",
      "listen_addresses": "*",
      "log_line_prefix": "%m [%p]: [%l-1] %c %u@%d ",
      "max_connections": "100",
      "port": "5432",
      "shared_buffers": "128MB",
      "ssl": "off",
      "timezone": "UTC",
      "wal_level": "replica",
      "work_mem": "4MB"
   }
}

위 쿼리의 json_build_object(함수를 사용하여 데이터베이스 정보를 JSON 형식으로 작성하고, json_object_agg 함수를 사용하여 Postgresql 데이터베이스 파라미터를 JSON 객체로 묶어 준다.
결과적으로 JSON 형식의 객체가 반환되는 걸 확인 할 수 있다.