How to convert a postgres json list to an all lowercase postgres array

Question

I have a table called "stuff" with a json column called "tags" that stores a list of tags, and a column called "id" which is the primary key for each row in the table. I am using a postgres database. For example, one row looks like this

   id | tags
   ---|------ 
   1  | ["tag1", "tag2", "tag3"]

I am trying to write a function that gets the rows which have a tag that matches the input tag variable called "s_tag". However, I want to convert all the tags to lowercase so that the tag matching is not case-sensitive.

I wrote the following test query:

     select id from stuff where '"tag1"' = any(select jsonb_array_elements(tags));

This query returned the ids of all the rows which had a tag called "tag1", but not "TAG1".

Then I read this post, so I tried

     select id from stuff where '"tag1"' like any(select jsonb_array_elements(tags));

But I got this error:

    ERROR: operator does not exist: unknown ~~* jsonb

So how can I convert the json list into a lowercase postgres array? Or is there some alternative way to search the list of tags in a case-insensitive way?

Jeremy · Accepted Answer · 2019-08-15 18:59:12Z

5

You can get the results as text instead of json:

select id 
from stuff 
where 'tag1' = any(select lower(jsonb_array_elements_text(tags)));

answered Aug 15, 2019 at 18:59

Jeremy

6,77322 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to convert a postgres json list to an all lowercase postgres array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related