Блог

Главная - Вопросы по программированию - Как выполнить поиск в поле json PostgreSQL по ключу/значению?

Как выполнить поиск в поле json PostgreSQL по ключу/значению?

#sql #json #postgresql #date

Вопрос:

У меня есть поле в моей базе данных PSQL с этими данными:

 {
  "1":{
    "wp_post_id":137840,
    "sync_at":{
      "date":"2021-02-23 22:02:35.958325",
      "timezone_type":3,
      "timezone":"Europe/Berlin"
    }
  },
  "3":{
    "wp_post_id":773,
    "sync_at":{
      "date":"2021-05-25 16:17:14.322988",
      "timezone_type":3,
      "timezone":"Europe/Berlin"
    }
  }
}

Я пытаюсь искать записи с sync_at датой ниже другой, но безуспешно…
Может быть, мой формат поля не подходит?!

Комментарии:

1. Вы хотите знать, соответствует ли какая-либо из записей условию, или вы хотите извлечь только те части JSON, которые соответствуют?

2. Я хочу знать, соответствует ли хотя бы одна запись поля условию (sync_at

3. Похоже, вы сбрасываете объект php datetime как есть в json, а затем пытаетесь выполнить математику даты с помощью pgsql. Было бы лучше, если бы вы сохранили что-то, что обе платформы могли бы понять, например, временные метки Unix или дату и время, преобразованные в utc

4. Это было бы намного проще с правильно нормализованной моделью данных

Ответ №1:

Самым простым способом сделать это был бы запрос JSONPATH (доступен с версии 12).:

 WHERE jsoncol @@ '$.*.sync_at.date < "2020-06-01 00:00:00"'

Запрос прост, но нет способа сделать его быстрым с помощью индекса.

Метки: