S3 to Redshift: Unknown boolean format

Question

Executing a copy command from S3 to Redshift, loading JSON files. I have some fields as bool in a new table I'm inserting into and always getting the following error: "Unknown boolean format"

My JSON is well parsed, ran a million tests on that already. I've tried passing in the boolean fields as:

false  //  "false"  //  "False"  //  0  //  "0"  //  null

But always get the same error, when executing:

select * from stl_load_errors;

err_code  err_reason
1210      Unknown boolean format

I've seen some comments about using IGNOREHEADER in my statement but that isn't an option because the files I'm dealing with are in a single row json format. Ignoring the head would basically mean not reading thefile at all. I have other tables working like this and work fine, but don't have any bool columns in those tables.

John Rotenstein John Rotenstein · Accepted Answer · 2017-02-16T02:34:09

The COPY from JSON Format documentation page provides an example that includes a Boolean:

{
    "id": 0,
    "guid": "84512477-fa49-456b-b407-581d0d851c3c",
    "isActive": true,
    "tags": [
        "nisi",
        "culpa",
        "ad",
        "amet",
        "voluptate",
        "reprehenderit",
        "veniam"
    ],
    "friends": [
        {
            "id": 0,
            "name": "Carmella Gonzales"
        },
        {
            "id": 1,
            "name": "Renaldo"
        }
    ]
}

The Boolean documentation page shows values similar to what you have tried.

S3 to Redshift: Unknown boolean format

1 Answers