'Store JSON array as TEXT array in INSERT INTO Postgres statement

Before, I had the following table schema.

CREATE TABLE cities (
    "city_id" BIGSERIAL PRIMARY KEY,
    "city_name" TEXT NOT NULL UNIQUE,
    "employees_names" JSONB NOT NULL
);

I use logstash (jdbc output plugin) to store data in psql. Here is my config (working). As you can see, I need to use the VALUES statement in order to store data.

statement => ["
INSERT INTO cities (
    city_name, 
    employees_names
) 
VALUES (
    ?, 
    CAST (? AS JSON)
)
",
"[doc][city_name]",
"[doc][employees_names]"
]

The data is stored under the following form. My Json data is just an array.

 city_name    |                    employees_names                     
--------------+--------------------------------------------------------
 new_york     | ["marc", "steven", "julia"]

Now, I want to store the employees_names as a text array (TEXT[]) and I want my table to look like the following.

CREATE TABLE cities (
    "city_id" BIGSERIAL PRIMARY KEY,
    "city_name" TEXT NOT NULL UNIQUE,
    "employees_names" TEXT[] NOT NULL
);

As I can't use fonctions (like json_array_elements_text()) in the VALUES statement, how can I do that ? What would my INSERT INTO statement look like ? Thanks !



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source