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?

• Erwin Brandstetter: Is misc->'names' "a list of text values" or an actual JSON array? What do you get for jsonb_typeof (misc->'names')? Also (as always) your version of Postgres, please.

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;