Convert an optional JSONB field to a text array?

Question

In my_table, the misc column is jsonb with default value {}. Some rows have misc.names as an array of text values; other rows do not have the misc.names key.

I want to copy that data to a separate names::text[] column, with a command like:

UPDATE my_table SET names = COALESCE(misc->>'names', ARRAY[]::text[])

In other words, "if the row has a list of text values in misc->>'names', put those in the names column, but if misc->>'names' is NULL in that row, put an empty text array in the names column."

However, this fails with:

ERROR: 42804: COALESCE types text and text[] cannot be matched

I don't understand what the error is saying. What am I doing wrong?

Answer 1

Assuming misc->'names' is an actual JSON array (not "a list of text values"), I suggest this custom translation function:

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
'SELECT ARRAY(SELECT jsonb_array_elements_text(_js))';

Detailed explanation:

• How to turn JSON array into Postgres array?

Then your UPDATE query can be:

UPDATE my_table
SET    names = COALESCE(jsonb_array_to_text_array(misc->'names'), '{}')
WHERE  names <> COALESCE(jsonb_array_to_text_array(misc->'names'), '{}');

Note the added (optional) WHERE clause to skip empty updates that would not change anything (at full cost). This is assuming names is defined NOT NULL. See:

• How do I (or can I) SELECT DISTINCT on multiple columns?

Answer 2

misc->>'names', while kind of appearing as an array, is actually text, not text[]. You would need to convert the jsonb array into a postgresql text array. The datatypes and a way to perform the conversion are explored in this DB-Fiddle, showing how a simple string_to_array conversion is probably what not what is desired, versus building an array from the results of a call to jsonb_array_elements_text.

CREATE TABLE my_table
(
    id          SERIAL,
    misc        jsonb DEFAULT '{}',
    expected    text[],
    names       text[]
);
INSERT  INTO my_table ( misc, expected )
VALUES  ( '{ "names": [ "A", "B", "C" ] }', ARRAY[ 'A', 'B', 'C' ] ),
        ( DEFAULT, ARRAY[]::text[] );
SELECT  misc, expected, names,
        misc->>'names',
        pg_typeof( misc->>'names' ),
        string_to_array( misc->>'names', ',' ),
        pg_typeof( string_to_array( misc->>'names', ',' ) ),
        ARRAY( SELECT jsonb_array_elements_text( misc->'names' ) ),
        pg_typeof( ARRAY( SELECT jsonb_array_elements_text( misc->'names' ) ) )
FROM    my_table;
UPDATE  my_table
    SET names = COALESCE( ARRAY( SELECT jsonb_array_elements_text( misc->'names' ) ), ARRAY[]::text[] );
SELECT  misc, expected, names
FROM    my_table;