Получаем данные в формате JSON из PostgreSQL на Go

В одном из проектов, у меня возникла необходимость получить разные варианты сводной статистики по одной из таблиц в формате JSON одним запросом. Да, именно так: не прочитать данные из поля типа JSON/JSONB, а конвертировать обычные строки в JSON. Изучив раздел документации PostgreSQL про работу с JSON, я нашёл все необходимые инструменты.

Пример 1

Запрос к БД

Задача: хотим получить количество записей с группировкой по датам (на основе поля created_at типа TIMESTAMP) в формате JSON, например вот так:

{
  "visits": {
    "2024-11-13": "250",
    "2024-11-14": "54",
    "2024-11-15": "263",
    "2024-11-16": "63",
    "2024-11-17": "26",
    "2024-11-18": "30",
    "2024-11-19": "14",
    "2024-11-20": "9",
    "2024-11-21": "4",
    "2024-11-22": "4"
  }
}

Пример запроса на SQL:

SELECT
    json_build_object(
        'visits', visits
    )
FROM (
    select json_object(
        array_agg(v1.created_date::text),
        array_agg(v1.visits::text)
    ) as visits
    from (
        select
            date(created_at) as created_date,
            COUNT(*) as visits
        from visits
        where link_id = 372
        group by created_date
        order by created_date asc
    ) v1
)

В данном случае, json_object() принимает два массива и попарно соединяет их элементы в качестве ключа и значения в объекте JSON. json_build_object() размещает полученный объект во внешнем объекте под ключом visits. В результате запроса, из БД придёт одна строка с одним столбцом json_build_object с результатом, похожим на приведённый выше образец JSON.

Работа из кода на Go

Далее мы рассмотрим, как выполнить написанный нами SQL-запрос из программы на Go, и декодировать полученный результат в соответствующую данным структуру. Я приведу только самые важные моменты кода. Полный пример аналогичной программы вы можете увидеть в замечательном посте Алекса Эдвардса.

// Определим тип данных, соответствующий формату, в котором мы получим JSON из БД:
type Visits struct {
    Visits map[string]string `json:"visits"`
}

// Наша структура должна реализовывать метод Scan(), чтобы декодировать
// данные при получении из БД
func (s *Visits) Scan(value interface{}) error {
    b, ok := value.([]byte)
    if !ok {
        return errors.New("type assertion to []byte failed")
    }
    return json.Unmarshal(b, &s)
}

// ...

func GetVisits() (*Visits, error) {
  query := fmt.Sprintf(`
    SELECT
    json_build_object(
      'visits', visits
    )
    FROM (
      SELECT json_object(
        array_agg(v1.created_date::text),
        array_agg(v1.visits::text)
      ) AS visits
      FROM (
        SELECT
          date(created_at) AS created_date,
          COUNT(*) AS visits
        FROM visits
        GROUP BY created_date
        ORDER BY created_date ASC
      ) v1
    )`,
  )

  ctx, cancel := context.WithTimeout(context.Background(), defaultQueryTimeout)
  defer cancel()

  rows, err := m.db.QueryContext(ctx, query)
  if err != nil {
    return nil, err
  }

  rows.Next()
  visits := Visits{}
  // Вот тут и понадобится написанный нами метод Scan(): полученные из БД
  // данные будут декодированы в переменную visits.
  err = rows.Scan(&visits)
  if err != nil {
    return nil, err
  }

  return &visits, nil
}

Пример 2

Запрос к БД

Задача: хотим получить из БД список записей в формате JSON в виде массива объектов, в таком виде:

{
  "oses": [
    {
      "os": "iOS",
      "visit_count": 343
    },
    {
      "os": "Android",
      "visit_count": 290
    },
    {
      "os": "Windows",
      "visit_count": 32
    },
    {
      "os": "MacOS",
      "visit_count": 19
    },
    {
      "os": "",
      "visit_count": 18
    },
    {
      "os": "Linux",
      "visit_count": 15
    }
  ]
}

Запрос для этого будет даже проще, чем в первом примере:

SELECT
    json_build_object(
        'oses', oses
    )
FROM (
    select json_agg(v1) as oses
    from (
        select os, count(os) as visit_count
        from visits
        where link_id = 372
        and (date(created_at) between '2024-11-13' and '2024-11-24')
        group by os
        order by visit_count desc
    ) v1
)

json_agg() превратит строки в массив объектов, где названия колонок – ключи. json_build_object() разместит этот массив в объекте по ключу oses. В результате запроса, из БД придёт одна строка с одним столбцом json_build_object с приведённым выше JSON.

Работа из кода на Go

Код будет аналогичен первому примеру, но нужно по другому определить структуру, которую мы будем декодировать JSON.

// Определяет объекты в массиве
type OsStatsItem struct {
    OS         string `json:"os"`
    VisitCount int64  `json:"visit_count"`
}

// Определяет общий формат JSON
type OsStats struct {
    Oses []*OsStatsItem `json:"oses"`
}

// Полностью аналогично первому примеру
func (s *OsStats) Scan(value interface{}) error {
    b, ok := value.([]byte)
    if !ok {
        return errors.New("type assertion to []byte failed")
    }
    return json.Unmarshal(b, &s)
}

Недостатки подхода

Заметка получила конструктивную критику от @Max@lor.sh, поэтому решил отметить здесь недостатки рассмотренного подхода.

• Двойное преобразование в JSON. Сначала данные конвертируются в JSON на уровне БД, потом парсятся в коде приложения, и, в конечном итоге, снова переводятся в JSON, чтобы отдать их из эндпоинта. Если в вашем случае это неприемлемо, не делайте так!
• Громоздкие запросы на SQL и код для обработки результатов на Go. Тут нечего добавить – действительно, могут возникнуть затруднения в поддержке такого кода.

Как обычно, нужно тщательно взвесить все "за" и "против", прежде чем отдать предпочтение в пользу этого или иного способа взаимодействия с БД.

Заключение

Любопытно, что мы можем объединить несколько подобных запросов в один. Так как в результате из БД возвращается одна строка с одной колонкой формата JSON, мы можем воспользоваться UNION ALL и одним запросом получить большое количество разнородной информации, сразу в JSON.

Мне очень интересно узнать, пригодился ли вам такой подход на практике и как он себя показал в вашем проекте. Пожалуйста, поделитесь своим опытом через форму ниже, а лучше – напишите в Мастодоне или в Телеграм!

Справочные материалы

• json_build_object
• json_object
• array_agg
• Using PostgreSQL JSONB with Go