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

Can Postgres filter using the LIKE operator on a JSONB array readonly column?

I am writing a SQL query. It’s a very complex query. But here I in this question, I will only extract out the part that I am struggling to make it easier to explain what I am struggling with.

I have a jsonb readonly array column in the select statement of my query as follow.

SELECT column_1, column_2, json_agg(column_3)::jsonb as item_ids from items

Please pay attention to json_agg(column_3)::jsonb.

The query return an array of text/ string for item_ids column. If I want to filter if any of the given parameter or string is equal to any of the element/ value/ string in the item_ids array, I can do something like this.

SELECT column_1, column_2, json_agg(column_3)::jsonb as item_ids from items 
WHERE item_ids ? 'item_1'

That will return the row where any of the element of item_ids array is exactly equal to ‘item_1’.

But I want something like like operator and I want it to be case insensitive.

Metaphorically, something like this.

SELECT column_1, column_2, json_agg(column_3)::jsonb as item_ids from items 
    WHERE ANY(item_ids) ILIKE '%item_1%'

How can I do that?

The query returns the data in the following format.

column_1   | column_2    | item_ids
------------------------------------------------------
row1 col 1 | row1 col 2  | [ item_1, item_2, item_3 ]
row2 col 1 | row2 col 2  | [ item_1, item_2, item_3 ]
Tags:

2

Answers


  1. 0

    Finding something inside a json array is generally less performant than attempting to use standard sql operations on a table.

    A viable option is to search for the existence of one such item among your data with an EXISTS operator. In this way, the WHERE clause will filter out all group_id values that don’t have pattern at least once, and the json arrays you’ll be building are only the ones you need.

    Here’s an example:

    SELECT group_id, 
       JSONB_AGG(column_3) AS item_ids 
FROM items i1
WHERE EXISTS(SELECT 1 FROM items i2 WHERE i1.column_id = i2.column_id ILIKE '<your_pattern>')
GROUP BY group_id

    On a side note, there are two problems with your queries:

    • the former two are missing non-aggregated fields in the GROUP BY clause
    • the WHERE clause of the third query will apply before the aggregation is carried out, and for this reason, such clause does not make sense if you’re attempting to work on the computed json
    Login or Signup to reply.
  2. 0

    ANY only works on the right hand side, so value ILIKE ANY patterns. There is no reversed operator where you could use pattern ??? ANY values (unless you create yourself a custom operator), see Postgresql ILIKE/LIKE ANY returns no results when using a pattern. You will need to use a subquery in your condition, which is easier done with an actual text[] than a jsonb holding an array of strings.

    SELECT column_1, column_2, item_ids::jsonb
FROM (
  SELECT *, array_agg(column_3) AS item_ids
  FROM items
  -- GROUP BY something, I assume
) AS agg_items
WHERE EXISTS(
  SELECT *
  FROM unnest(item_ids) AS item_id
  WHERE item_id ILIKE '%item_1%'
);

    You might be able to rewrite this into a HAVING condition depending on how exactly your aggregation works.

    Alternatively, if you’re dead-set on using a jsonb value, you can use a json path query with the like_regex operator:

    SELECT column_1, column_2, item_ids
FROM (
  SELECT *, jsonb_agg(column_3) AS item_ids
  FROM items
  -- GROUP BY something, I assume
) AS agg_items
WHERE item_ids @? '$[*] ? (@ like_regex "item_1" flag "i")';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search