Handling variable fields in a CSV file with JSON? Im trying to figure out the engineering to get a CSV file into PostgreSQL.
Requirements:


The CSV has X number of predetermined required fields
The CSV file has Y number of dynamic fields


Example:


TenantCode,DomainCode,PersonCode,ExtraField1,ExtraField2,ExtraFieldN
"CUST1","STUD","C0001","Donald","Duck","M"
"CUST1","STUD","C0002","Diana","Duck","F"


into this format (in PostgreSQL 10)

TenantCode---DomainCode---PersonCode---ExtendedFields(JSONB)


CUST1---STUD---C0001---  {"ExtraField1":"Donald","ExtraField2":"Duck","ExtraFieldN":"M"}
CUST1---STUD---C0002---   {"ExtraField1":"Diana","ExtraField2":"Duck","ExtraFieldN":"F"}


My original thought would be to (in Python) use Pandas to convert the entire file to json, then use PostgreSQL to COPY into a staging table, then use PostgreSQL SQL to parse out the required fields and insert into the destination table.
I wouldn't do all that. I would just use python to transform the CSV file into another CSV where all the variable fields are a string of JSON.

Create the right table with the jsonb column and load direct into it. To do that use psql and \COPY. You can even create a program that reads from STDIN does the reformatting and outputs to STDOUT. And sits in the pipeline. Or you can write a quick perl script to get this done,

perl reformat.pl stupid1.csv stupid2.csv |
  psql -d test -e '\COPY mymastertable FROM stdin WITH ( format = csv )'


Or whatever.