Как прочитать массив объектов из колонки jsonb в Spring data?

Question

У меня есть в бд поле типа jsonb, состоящее из массива объектов.

Jpa не совсем работает с таким типом данных, то есть например, если мне нужно найти все объекты в этом json по какому-то критерию, то просто так я не смогу через JpaRepository вернуть List этих объектов, нужно будет сначала вернуть List<Object>. И далее уже через mapper конвертировать в нужную мне модель. Понятно, что не совсем сложная операция. Но я решил пойти дальше и попробовать такое: может как-то можно унаследоваться от какого-нибудь класса Spring Jpa, чтобы реализовать точно такое же поведение, но в своей реализации, то есть сразу пишет @Query как обычно и возвращаем например List<Интересующая модель>. То есть конвертация происходит в моей реализации. Может метод помечать новой аннотацией, например @JsonToList и параметр как раз необходимый тип модели.

У меня вопрос к более опытным джавистам, вообще такое возможно? Стоит ли пыжиться? Может скажете, что как раз это нерентабельно и заморачиваться не нужно, проще сделать класс хэлпер и через него переводить.

Answer 1

В PostgreSQL прочитать массив объектов из колонки jsonb можно с помощью встроенной функции jsonb_array_elements. Далее поля объектов разложить по отдельным колонкам и выгрузить их в java в виде List<Map<String, Object>> с помощью метода queryForList класса JdbcTemplate.

См. Query for array elements inside JSON type

Пример: в таблице sql в колонке типа jsonb могут находиться массивы объектов или просто объекты:

id (serial) | jsonb_column (jsonb)
------------|---------------------
1           | {"id": 1, "profit": 100, "person": {"age": 21, "name": "john"}}
------------|---------------------
2           | [
            |  {"id": 2, "profit": 200, "person": {"age": 22, "name": "petr"}}, 
            |  {"id": 3, "profit": 300, "person": {"age": 23, "name": "sidr"}}
            | ]

SQL запрос:

SELECT
    nt.id,
    nt.jsonb_column -> 'id' AS json_id,
    nt.jsonb_column -> 'profit' AS profit,
    nt.jsonb_column -> 'person' -> 'age' AS person_age,
    nt.jsonb_column -> 'person' -> 'name' AS person_name
FROM
(
    SELECT id, jsonb_column
    FROM public.newtable
    WHERE jsonb_typeof(jsonb_column) = 'object'

    UNION ALL

    SELECT id, jsonb_array_elements(jsonb_column) AS jsonb_column
    FROM public.newtable
    WHERE jsonb_typeof(jsonb_column) = 'array'
) AS nt

Возвращаемая таблица значений:

| id | json_id | profit | person_age | person_name |
|----|---------|--------|------------|-------------|
| 1  | 1       | 100    | 21         | "john"      |
| 2  | 2       | 200    | 22         | "petr"      |
| 2  | 3       | 300    | 23         | "sidr"      |

Вызов из java:

@Autowired
private JdbcTemplate jdbcTemplate;

public List<?> executeQueryForList(String query) throws DataAccessException {
    return jdbcTemplate.queryForList(query);
}

Возвращаемый тип данных: List<Map<String, Object>>.

БЛОГ НА HUSL

Как прочитать массив объектов из колонки jsonb в Spring data?

Ответы (1 шт):