Блог

Главная - Вопросы по программированию - Создание материализованного представления из таблицы, содержащей JSONB в PSQL

Создание материализованного представления из таблицы, содержащей JSONB в PSQL

#json #postgresql #psycopg2 #materialized-views

Вопрос:

Здравствуйте и заранее спасибо за помощь,

Я пытаюсь создать материализованное представление с помощью PSQL, используя библиотеку python psycopg2 со следующей структурой:

 <materialized_view name>
 time TIMESTAMPTZ NOT NULL,                                            
 robot_id TEXT,                                            
 data JSONB

Таблица, из которой я хочу создать материализованное представление, создается с помощью следующего запроса:

 CREATE TABLE IF NOT EXISTS mainTable (
 time TIMESTAMPTZ NOT NULL,                                            
 robot_id TEXT,                                            
 data JSONB);

Затем, чтобы проверить, можно ли сгенерировать запрос, выходные данные которого совпадают со структурой материализованного представления, я выполнил следующий запрос:

 SELECT time_bucket('30s',time) as Bucket, robot_id, 
       json_build_object('value', avg((data->>'value')::numeric),'field', data->>'field') as data
FROM mainTable
WHERE time >= '2021-05-20 09:00:00' AND time <= '2021-05-20 10:00:00'
GROUP BY data->>'field', bucket, robot_id
ORDER BY 1
LIMIT 10;

Результат этого последнего запроса соответствует ожиданиям, и его можно увидеть здесь:
Результат выполнения предыдущего запроса

Однако, когда я хочу включить тот же запрос для создания материализованного представления, я получаю некоторые ошибки в ГРУППЕ, которые я не знаю, как решить.

Запрос, который я использую для создания материализованного представления, выглядит следующим образом:

 CREATE MATERIALIZED VIEW IF NOT EXISTS mainTable_1sec
                             WITH (timescaledb.continuous) AS
                             SELECT time_bucket('1s', time) as Bucket,
                                    json_build_object('field', data->>'field', 'value',avg(cast(data->>'value' as double precision))) as avg_data ,
                                    robot_id
                             FROM mainTable
                             GROUP BY Bucket, data->>'field', robot_id
                             WITH NO DATA;

Когда я выполняю эту команду, материализованное представление создается правильно, но когда я выполняю запрос:

 SELECT * 
FROM mainTable_1sec 
LIMIT 10;

Я получаю следующую ошибку:

 ERROR:  ORDER/GROUP BY expression not found in targetlist

Еще раз спасибо за вашу помощь,

Джорди.

Метки: ,