Блог

Главная - Вопросы по программированию - Postgres: запрос диапазона для вложенного столбца jsonb

Postgres: запрос диапазона для вложенного столбца jsonb

#json #postgresql #jsonb

#json #postgresql #jsonb

Вопрос:

Я использую postgres 9.5.4.

Из приведенного ниже примера данных json, хранящихся в столбце jsonb, я хотел бы выполнить поиск записей, соответствующих o['mid'] > 7000

Это образец записи одного пользователя. Таких пользователей будут миллионы.

 {
  "uid": 14105529,
  "o": [
    {
      "mid": 6551,
      "ac": 1913,
      "ip": "144.36.233.44",
      "adw": 5,
      "at": 133000,
      "ad": 151015,
      "aid": 0
    },
    {
      "mid": 7552,
      "ac": 1913,
      "ip": "144.36.233.44",
      "adw": 5,
      "at": 133000,
      "ad": 151015,
      "aid": 0
    },
    {
      "mid": 7553,
      "ac": 1913,
      "ip": "144.36.233.44",
      "adw": 5,
      "at": 133000,
      "ad": 151015,
      "aid": 0
    }
  ]
}

Ответ №1:

 with a_table(jdata) as ( 
values (
    '{
        "uid":14105529,
        "o":[
            {"mid":6551,"ac":1913,"ip":"144.36.233.44","adw":5,"at":133000,"ad":151015,"aid":0}, 
            {"mid":7552,"ac":1913,"ip":"144.36.233.44","adw":5,"at":133000,"ad":151015,"aid":0},
            {"mid":7553,"ac":1913,"ip":"144.36.233.44","adw":5,"at":133000,"ad":151015,"aid":0} 
        ] }'::jsonb
    )
)

select jdata->'uid' as uid, value
from a_table, jsonb_array_elements(jdata->'o')
where (value->>'mid')::int > 7000;

   uid    |                                              value                                               
---------- --------------------------------------------------------------------------------------------------
 14105529 | {"ac": 1913, "ad": 151015, "at": 133000, "ip": "144.36.233.44", "adw": 5, "aid": 0, "mid": 7552}
 14105529 | {"ac": 1913, "ad": 151015, "at": 133000, "ip": "144.36.233.44", "adw": 5, "aid": 0, "mid": 7553}
(2 rows)

Запрос будет действительно дорогостоящим для большого набора данных из-за необходимости отмены вложения массива json jsonb_array_elements() .
Нет индекса, который вы могли бы использовать для ускорения.

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

1. Большое спасибо, Клин. Это работает для меня. Как вы упомянули, необходимо проверить производительность на большом наборе данных.

2. @ klin, столкнулся с проблемой при выполнении этого запроса в кластере citus. Нужна помощь. postgres=# выберите count(1) из jsontest, jsonb_array_elements(data-> ‘o’) где (значение->> ‘mid’)::int>7000; ОШИБКА: не удается выполнить распределенное планирование для этого запроса ПОДРОБНЕЕ: сложные табличные выражения в настоящее время не поддерживаются

3. К сожалению, это не стандартное сообщение об ошибке Postgres. Похоже, citus имеет дополнительные ограничения, см. Эту проблему citus .

Метки: