(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:

I am choosing to not create a separate table for the options because there’s really no need for me to do so in my application, so I would like to not have to do that if possible.

My definition of the table:

CREATE TABLE statements(
statement_id INT GENERATED BY DEFAULT AS IDENTITY,
statement VARCHAR(100) NOT NULL,
options JSON [],
PRIMARY KEY(statement_id)
);

How I import the CSV file into the table:

copy statements(statement_id, statement, options)
from 'D:Projectsprojectfilesstatements.csv'
delimiter ','
csv header;

How options is formatted in my CSV:

{("label": "Not at all True","value": 1),("label": "Hardly True","value": 2),("label": "Moderately True","value": 3),("label": "Exactly True","value": 4)}

Attempt 1

Using the setup I’ve mentioned above, this is the error I get:

ERROR:  malformed array literal: "{("label": "Not at all True","value": 1),("label": "Hardly True","value": 2),("label": "Moderately True","value": 3),("label": "Exactly True","value": 4)}"
DETAIL:  Unexpected array element.
CONTEXT:  COPY asmt_questions, line 2, column options: "{("label": "Not at all True","value": 1),("label": "Hardly True","value": 2),("label": "Moderately T..."

I found this solution, and edited my options format in the CSV to be:

array[("label": "Not at all True","value": 1),("label": "Hardly True","value": 2),("label": "Moderately True","value": 3),("label": "Exactly True","value": 4)]::json[]

and got the following error:

ERROR:  malformed array literal: "array[("label": "Not at all True","value": 1),("label": "Hardly True","value": 2),("label": "Moderately True","value": 3),("label": "Exactly True","value": 4)]::json[]"
DETAIL:  Array value must start with "{" or dimension information.
CONTEXT:  COPY asmt_questions, line 2, column options: "array[("label": "Not at all True","value": 1),("label": "Hardly True","value": 2),("label": "Moderat..."

Attempt 2

I couldn’t find any solutions that were related to this problem, but I did notice that the solution I mentioned above used composite types, so I created a composite type:

CREATE TYPE statement_option AS (
    label VARCHAR(20),
    value INT
);

and changed the statement table schema to be

CREATE TABLE statements(
    statement_id INT GENERATED BY DEFAULT AS IDENTITY,
    statement VARCHAR(100) NOT NULL,
    options statement_option [],
    PRIMARY KEY(statement_id)
    );

I got the same errors listed above.

Attempt n

After trying to search for a solution to these errors, I couldn’t find one that was specific to this situation (a lot of them had to do with procedures and functions). So I tried many different combinations of the above setups.

I tried using brackets to represent an array in the CSV (ex: [{},{}]), I changed the [] to ARRAY in the statement table schema, I used single quotes instead of double quotes in the CSV file, etc. All to no avail.

At this point, I feel like I’ve exhausted the options I can try when it comes to this specific setup. I have a hunch that I might have to just do it line by line, or just create a procedure to read them in.

But I was wondering if there was something that I’m missing, or something that I’m doing wrong?

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}""}