Use JSON array in SQL query in the repository

Question

In my PostgreSQL table I have a 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 elements we
where parts::text like '%"CHEST"%'

However, it doesn't work in my repository, I'm not sure but it's probably jpql:

@Override
public List<ElementView> test() {
    return entityManager.createQuery(
                    "SELECT DISTINCT w FROM ElementView w " +
                            "WHERE we.parts::text like '%CHEST%'", ElementView.class)
            .getResultList();
}

Is it possible to use this query (or another) to check the json array in the repository?

Answer 1

See if this example solves it :

SELECT * , a.json->'parts'
FROM (SELECT '{"parts":"CHEST"}'::JSON as json) AS a
WHERE  a.json->>'parts' = 'CHEST';

Notice there is two operators -> for selection and ->> for predicates. They doesnt work when get their roles confused.

Answer 2

The query "in your repository" can't work because the table alias is w, but you reference we:

SELECT DISTINCT w FROM ElementView w
WHERE we.parts::text like '%CHEST%'

Also not sure about your upper-case letters in the search term '%CHEST%'. You might want:

... WHERE w."parts"::text ILIKE '%chest%'

See:

• Are PostgreSQL column names case-sensitive?
• LOWER LIKE vs iLIKE

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."parts" ? 'CHEST'

See:

• Index for finding an element in a JSON array