В моей базе данных Postgres есть таблица под названием ts_kv
id | key | json_v
——————————----------------------
UUID | Integer | json
Значение json_v
такое
[{"ts":61000,"eui64”:”f4dddhhsd”,”volume":0}]
Я хочу найти все последние записи для каждой отдельной записи eui64.
Допустим, у меня есть несколько записей в этой базе данных ниже json_v.
[{"ts":10,"eui64":"f4ce777776470","volume":0}]
[{"ts":10,"eui64":"f4ce777776471","volume":0}]
[{"ts":10,"eui64":"f4ce777776472","volume":0}]
[{"ts":20,"eui64":"f4ce777776471","volume":50}]
[{"ts":20,"eui64":"f4ce777776472","volume":50}]
У меня есть запрос sql для получения последней записи для каждого eui64 из таблицы ts_kv,
WITH ranked_data AS (
SELECT
json_v,
json_v->>'eui64' AS eui64,
(json_v->>'ts')::bigint AS ts,
ROW_NUMBER() OVER (PARTITION BY json_v->>'eui64' ORDER BY (json_v->>'ts')::bigint DESC) AS rn
FROM
ts_kv
)
SELECT
*
FROM
ranked_data
WHERE
rn = 1;
который возвращает
[{"ts":10,"eui64":"f4ce777776470","volume":0}]
[{"ts":20,"eui64":"f4ce777776471","volume": 50}]
[{"ts":20,"eui64":"f4ce777776472","volume":50}]
Однако когда я попытался сделать это запросом, используя @Query
,
@Query(value = "WITH ranked_data AS ( " +
" SELECT " +
" json_v, " +
" json_v->>'eui64' AS eui64, " +
" (json_v->>'ts')::bigint AS ts, " +
" ROW_NUMBER() OVER (PARTITION BY json_v->>'eui64' ORDER BY (json_v->>'ts')::bigint DESC) AS rn " +
" FROM " +
" ts_kv " +
") " +
"SELECT " +
" * " +
"FROM " +
" ranked_data " +
"WHERE " +
" rn = 1", nativeQuery = true)
List<TsKvEntity> findLatest();
Выдает ошибку
{
"status": 500,
"message": "could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet",
"errorCode": 2,
"timestamp": "2024-06-13T20:56:13.084+00:00"
}
И затем я проверил консоль
WITH ranked_data AS ( SELECT
json_v,
json_v->>'eui64' AS eui64,
(json_v->>'ts'):bigint AS ts,
ROW_NUMBER() OVER (PARTITION
BY
json_v->>'eui64'
ORDER BY
(json_v->>'ts'):bigint DESC) AS rn
FROM
ts_kv ) SELECT
*
FROM
ranked_data
WHERE
rn = 1
Hibernate:
WITH ranked_data AS ( SELECT
json_v,
json_v->>'eui64' AS eui64,
(json_v->>'ts'):bigint AS ts,
ROW_NUMBER() OVER (PARTITION
BY
json_v->>'eui64'
ORDER BY
(json_v->>'ts'):bigint DESC) AS rn
FROM
ts_kv ) SELECT
*
FROM
ranked_data
WHERE
rn = 1
2024-06-13 13:56:13,082 [http-nio-0.0.0.0-8080-exec-2] WARN o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 42601
2024-06-13 13:56:13,082 [http-nio-0.0.0.0-8080-exec-2] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ERROR: syntax error at or near ":"
Position: 108
Однако я не смог определить, где строка вызывает ошибку. Есть ли у кого-нибудь идеи?
Ваше значение содержит типографские кавычки, где допустимым синтаксисом являются только простые двойные кавычки:
Неправильный:
[{"ts":61000,"eui64”:”f4dddhhsd”,”volume":0}]
Верно:
[{"ts":61000,"eui64":"f4dddhhsd","volume":0}]
test=> SELECT ascii('"') AS plain, ascii('”') AS typo;
plain | typo
-------+------
34 | 8221
Значение не выдает ошибку сразу, поскольку последняя двойная кавычка верна, поэтому оно анализируется следующим образом:
test=> SELECT * FROM jsonb_each('{"ts": 61000, "eui64”:”f4dddhhsd”,”volume": 0}');
key | value
----------------------------+-------
ts | 61000
eui64”:”f4dddhhsd”,”volume | 0
Это недостаток UNICODE: множество одинаковых символов, которые только сеют путаницу.
Еще одна синтаксическая ошибка в вашем тесте в консоли:
Синтаксис короткого приведения ::bigint
не
. Видеть::bigint
При этом есть более дешевый и короткий путь...
чтобы получить последнюю запись для каждого eui64 из таблицы ts_kv
SELECT DISTINCT ON (json_v->>'eui64')
json_v -- more?
FROM ts_kv
ORDER BY json_v->>'eui64', (json_v->>'ts')::bigint DESC;
Видеть:
Спасибо за ответ, но [{"ts":61000,"eui64":"f4dddhhsd","volume":0}]
— это массив, а не объект, поэтому результат запроса неверен. Просто интересно, можно ли запросить его, не меняя форму данных?
@ERTY: Какой результат запроса? Мой DISTINCT ON
вопрос? Снимите оболочку массива шума с помощью json_v->0->>'eui64'
и т. д.
Поскольку
:bigint
может быть заполнителем параметра, может ли это быть так?