'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 |
---|