How to insert a Postgres Array from a Stringified JSON Array

Richard Coates (he/him) — Fri, 06 Mar 2020 16:13:33 +0000

In an endpoint in a project that I’ve been working on this week, we receive a JSON payload from a call to our API, but the Postgresql database with which we’re working stores the contents of this payload as a Postgres Array.

To make matters a little more complicated, the value field is sometimes a single string, rather than an array, and so the .sql file takes either a stringified JSON or a string as its argument.

I couldn’t find a simple explanation for how to work with this particular arrangement, because I don't imagine it's that common. However, just in case it's useful for anyone else, here's how to do it (and hello me in the future, when I’ve forgotten how to do this!).

Consider the following table:

CREATE TYPE v_type AS ENUM ('text', 'list');
CREATE TABLE demo_table (
  id bigserial NOT NULL,
  value_type v_type NOT NULL,
  text_value text NULL,
  list_value _text NULL
);

And we want to insert either a text_value or a list_value depending on the value_type column.

The insert statement must look like this:

    INSERT 
    INTO
        demo_table
        (value_type, text_value, list_value) 
    values
        ($1,  case 
            when $1 = 'text' then   $2 
            else null 
        end, case 
            when $1 = 'list' then   ARRAY(SELECT
                json_array_elements_text(cast($2::text as json))) 
            else null 
        end);

The reason that the second part is so complicated is as follows:

Postgres functions are typesafe, and so each function needs to ensure that its inputs are of the correct type. Therefore, you need to specify that $2 will be a text getting cast to JSON, which can then be fed into json_array_elements_text (which will only accept a JSON). Note that this may error at runtime if the text isn’t a valid JSON structure (but that doesn't concern us - it will be processed beforehand).
The only way in Postgres (that I could find) to turn a JSON into an array is to use json_array_elements to expand the JSON array to a set of text values, and then ARRAY to take the text values and make them into an array.

Credit to Erwin Brandstetter for this StackExchange answer, which provided the bulk of my research.

DEV Community: Richard Coates (he/him)

How to insert a Postgres Array from a Stringified JSON Array