Question 90459 in PostgreSQL Question posted in
The official documentation can be found here.

Use JSON array in SQL query in the repository – Postgresql

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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:

    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:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search