How to load a JSON file to a single column of a Redshift table

Question

I am trying to load the below JSON sample to a single column in Redshift table from S3 as a Proof of Concept. However it is not working successfully. Any help on the same would be of great help.

(This is just a sample JSON with random values to check if it is working)

{
    "Name":"ABC",
    "Department":"HR",
    "Rollno":1,
    "Salary":1000,
    "RS Factor":4,
    "Expected Value":8.1,
    "Comment": "", 
    "Correction Factor": 1,
    "Fruit":"Apple",
    "Size":"Large",
    "Color":"Red"
}

COPY command used:

COPY "schema"."table" ("column") FROM 's3://bucketname/file.json' 
WITH CREDENTIALS AS 'aws_access_key_id=XXXXXX;aws_secret_access_key=XXXXXX' 
MAXERROR AS 0 DATEFORMAT AS 'auto' TIMEFORMAT AS 'auto' 
ACCEPTANYDATE TRIMBLANKS 
EMPTYASNULL 
BLANKSASNULL 
COMPUPDATE OFF 
STATUPDATE OFF ROUNDEC 
JSON AS 'auto' 
ENCODING AS UTF8 ACCEPTINVCHARS AS '?' 
IGNOREHEADER AS 0

Answer 1

You need to add json 'noshred' instead of auto . Thats all . it will load the json to single column without breaking it into multiple column

COPY "schema"."table" ("column") FROM 's3://bucketname/file.json' 
WITH CREDENTIALS AS 'aws_access_key_id=XXXXXX;aws_secret_access_key=XXXXXX' 
MAXERROR AS 0 DATEFORMAT AS 'auto' TIMEFORMAT AS 'auto' 
ACCEPTANYDATE TRIMBLANKS 
EMPTYASNULL 
BLANKSASNULL 
COMPUPDATE OFF 
STATUPDATE OFF ROUNDEC 
JSON 'noshred'
ENCODING AS UTF8 ACCEPTINVCHARS AS '?' 
IGNOREHEADER AS 0