How to lower-case all the elements of a JSONB array of strings of each row in a table

Question

I have a table with a field called "data" which is of JSONB type. The content of "data" is an object with one of the fields called "associated_emails", which is an array of strings.

I need to update the existing table so that the content of "associated_emails" is all lower-case. How to achieve that? This is my attempt so far (it triggers error: ERROR: cannot extract elements from a scalar)

update mytable my
set
    "data" = safe_jsonb_set(
        my."data",
        '{associated_emails}',
        to_jsonb(
            lower(
                (
                    SELECT array_agg(x) FROM jsonb_array_elements_text(
                        coalesce(
                            my."data"->'associated_emails',
                            '{}'::jsonb
                        )
                    ) t(x)
                )::text[]::text
            )::text[]
        )
    )
where
  my.mytype = 'something';

Answer 1

You would like to use JSONB_SET and UPDATE the column with something like given below below:

UPDATE jsonb_test 
SET    data = JSONB_SET(data, '{0,associated_emails}', 
                        JSONB(LOWER(data ->> 'associated_emails'::TEXT)));