Convert an optional JSONB field to a text array?



  • 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?



  • 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 https://www.db-fiddle.com/f/f9Pf5dWuffzR5o3Xh6GR2U/1 , 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;




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 3
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2