A
A
arionaRu2020-10-15 23:59:07
PostgreSQL
arionaRu, 2020-10-15 23:59:07

Select records whose jsonb array contains all elements of the array?

Hello! Imagine that we have a table, one of the fields of which contains an array of type jsonb.
Something like this

CREATE TABLE cats
(
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
properties JSONB NOT NULL
)


And domain entity

public Cat {
@Id
@GeneratedValue
private Long id;
private String name;
@Type(type = "jsonb")
private List<String> properties;
}


How can I choose such cats that have all the given properties?

For one property, I got this:

@Query(value = "select * from cats where jsonb_exists_any(properties,array[:property])" , nativeQuery = true)
    List<Cats> searchByField(@Param("property") String property);


But it doesn't work for an array.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question