Nested select for aggregate jsonb children, SQL/HQL

Question

I have such task: in spring/hibernate/postgres I have a entity(aggregate) that contains jsonb with children codes. Like in a container can be 100 blocks of sigarets, every one contains 10 packs and all of them (all 3 level) everyone have a uniqe code and located in one DB table.

@Entity
@Table(name = "code")
@Getter
@Setter
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
public class CodeDO {

    @Id
    private String code;

    private String status;

    private String type;

    @Type(type = "jsonb")
    @Column(columnDefinition = "jsonb")
    private JsonNode children;         // contains {code1, code2, code3}
}

I need to write HQL or SQL query to get Jsonb and for every element(child) SELECT * FROM code. Only for children 1 level down. Now I have something like that:

    @RequiredArgsConstructor
    @Service
    public class CodeServiceImpl implements CodeService {

    private final CodeRepository codeRepository;

        public List<List> getCodeFromAggregateChildren(String id, KmListRequest req) {
            JsonNode jsonNode = codeRepository.getById(id).getChildren();
            List<String> idList = new ObjectMapper().convertValue(jsonNode, List.class);
            List<JsonNode> childNodes= idList.forEach(childId -> codeRepository.getById(childId));
            return childNodes.stream().map(child -> new ObjectMapper().convertValue(child, List.class)).collect(Collectors.toList()); 
    }
}

So it works like 2 queries. First: get children, second: select for every children. The task is not to touch DB twice, combine this 2 queries in one.

score 1 · Answer 1 · answered Mar 30 '20 at 11:34

I've found an article that might be useful How to query jsonb. Basically, you'd do in your repository class something like:

@Query("SELECT * FROM code WHERE children ->> 'code' = :code", nativeQuery=true)
List<Code> findAllByCode(@Param("code") String code);

Unfortunately I could not find any help with HQL query, only native.

Nested select for aggregate jsonb children, SQL/HQL

1 Answers1