In my PostgreSQL table I have a body_parts column of type json. I want to check if the given argument is contained in my json column.
This query works in the database:
select * from worklist_elements we
where body_parts::text like '%"CHEST"%'
However, it doesn't work in my repository, I'm not sure but it's probably jpql:
@Override
public List<WorklistElementView> test() {
return entityManager.createQuery(
"SELECT DISTINCT w FROM WorklistElementView w "
"WHERE we.bodyParts::text like '%CHEST%'", WorklistElementView.class)
.getResultList();
}
Is it possible to use this query (or another) to check the json array in the repository?
CodePudding user response：
See if this example solves it :
SELECT * , a.json->'body_parts'
FROM (SELECT '{"body_parts":"CHEST"}'::JSON as json) AS a
WHERE a.json->>'body_parts' = 'CHEST';
Notice there is two operators -> for selection and ->> for predicates. They doesnt work when get their roles confused.
CodePudding user response：
The query "in your repository" can't work because the table alias is w, but you reference we:
SELECT DISTINCT w FROM WorklistElementView w WHERE we.bodyParts::text like '%CHEST%'
Also not sure about your upper-case letters in the unquoted identifier bodyParts and in the search term '%CHEST%'. You might want:
... WHERE w."bodyParts"::text ILIKE '%chest%'
See:
But this is hugely expensive and unspecific. Depending on what "contained" is supposed to mean exactly, and if your JSON column is actually type jsonb (much) more efficient queries with index support are possible. Like ...
Does "CHEST" exist as top-level key or array element (not as value) within the jsonb column?
... WHERE w."bodyParts" ? 'CHEST'
See: