Database Administration
- postgresql json array csv composite-types
- Updated Fri, 20 May 2022 18:47:50 GMT
-
(PostgreSQL 11) Reading in an array of JSON objects (or composite types) from a CSV file into Postgres
So I am trying to import rows into a table using a CSV file, but have been running into problems I haven't been able to solve. I would like one column of a row to be an array of JSON objects.
How I would generally like my table to look like:
| statement_id INT | statement VARCHAR(100) | options JSON[] |
|---|---|---|
| 1 | I am cool | [{label: 'Not true', value: 1}, {label: 'Somewhat True', value: 2}, {label: 'Very true', value: 3}] |
Solution
This works for me:
My CSV
id;json
1;{"{\"label\": \"Not at all True\",\"value\": 1}","{\"label\": \"Hardly True\",\"value\": 2}","{\"label\": \"Moderately True\",\"value\": 3}","{\"label\": \"Exactly True\",\"value\": 4}"}
2;{"{\"label\": \"Not at all True\",\"value\": 1}","{\"label\": \"Hardly True\",\"value\": 2}","{\"label\": \"Moderately True\",\"value\": 3}","{\"label\": \"Exactly True\",\"value\": 4}"}
My Table:
create table test_json_list (id integer, j json[]);
My COPY statement:
COPY test_json_list (id, j) FROM '/tmp/json.csv' WITH (DELIMITER ';', HEADER, FORMAT CSV, QUOTE '|');
Explanation: The array literal in PostgresSQL is a list of elements between curly braces, ex: {1,2,3} or {'a','b','c'}
I didn't find a clear explanation in the docs of the reason why the COPY command wants to have the JSON array elements quoted in double quotes, just took the output of a COPY TO command as reference.
I added a separator that doesn't conflict much with your JSON, but it is not very important.
I added a pipe QUOTE character to avoid one additional level of escaping of double quotes that would otherwise be required as the default CSV quote character is also a double quote.
My suggestion to simplify all this slightly would be to import a JSON array instead of an ARRAY (Postgres array) of JSONs if possible by your application logic.