Redshift/Postgres: how can I ignore rows that generate errors? (Invalid JSON in json_extract_path_text)

Create a python UDF:

create or replace function f_json_ok(js varchar(65535)) 
returns boolean
immutable
as $$
    if js is None: 
        return None

    import json
    try:
        json.loads(js)
        return True
    except:
        return False
$$ language plpythonu

Use it like so:

select *
from schema.table
where 'DesiredValue' = 
    case 
        when f_json_ok(json_column) then json_extract_path_text(json_column, 'Key') 
        else 'nope' 
    end 

Edit: it seems like Redshift only supports Python UDFs so this answer will not work. I'm going to leave this answer here for posterity (and in the event someone finds this who isn't using Redshift).

Potentially relevant: here is a plpgsql function which will try to decode JSON and return a default value if that fails:

CREATE OR REPLACE FUNCTION safe_json(i text, fallback json) RETURNS json AS $$
BEGIN
    RETURN i::json;
EXCEPTION
    WHEN others THEN
        RETURN fallback;
END;
$$ LANGUAGE plpgsql IMMUTABLE RETURNS NULL ON NULL INPUT;

Then you can use it like this:

SELECT
    …
FROM (
    SELECT safe_json(my_text, '{"error": "invalid JSON"}'::json) AS my_json
    FROM my_table
) as x

To guarantee that you'll always have valid JSON

I assume the JSON data is actually stored in a TEXT column rather than a JSON column (otherwise you would not have been able to store non-JSON in there in the first place).

If there is some pattern to the data that would allow you to make a regex that detects the valid rows, or the invalid ones, then you could use a CASE statement. For example:

SELECT CASE
    WHEN mycol !~ 'not_json' THEN json_extract_path_text(mycol, ....)
    ELSE NULL
END AS mystuff
...

replacing not_json with a regex that detects the non-JSON formatted values.

This may or may not be practical depending on the format of your data.

According to the answers on this question it is apparently possible to completely verify arbitrary JSON data using some regex implementations, but alas not the one used by postgresql.

Update: The UDF solution seems perfect. At the time I wrote this, that answer wasn't there. This one is just some work around methods.

Although json_extract_path_text can't ignore errors, but Redshift's COPY have a MAXERROR Parameter.

So, you can use something like this instead:

COPY raw_json FROM 's3://data-source' 
CREDENTIALS 'aws_access_key_id;aws_secret_access_key'
JSON 's3://json_path.json'
MAXERROR 1000;

The next pitfall is in json_path.json file: you can't use a $ to specify the root element:

{
    "jsonpaths": [
        "$['_id']",
        "$['type']",
        "$" <--------------- this will fail.
    ]
}

So, it would be convenience to have a "top-level" element containing other fields, like this: (So, $['data'] is everything on your record)

{
    "data": {
        "id": 1
        ...
    }
}
{
    "data": {
        "id": 2
        ...
    }
}

If you can't change the source format, Redshift's UNLOAD will help:

UNLOAD ('select_statement')
TO 's3://object_path_prefix'

It's easy to use a select_statement to concatenate: { "data" : + old string + }...

Then, Redshift rocks again!

Amazon Redshift now supports a final null_if_invalid argument in json_extract_path_text to support the desired behavior.

For example, to parse a json_column column and extract the my_field key from it, and ignore errors, use the following:

select json_extract_path_text(json_column, 'my_field', true) from my_table;

Redshift Reference

Redshift is missing a lot of Postgres functions such as error handling.

The way I handle this is:

1. Use CREATE TABLE AS to create a 'fixup' table with the JSON field and whatever the key is on the main table you're trying to query. Make sure you set the DISTKEY and SORTKEY to your JSON field.

2. Add two columns to my fixup table: valid_json (BOOLEAN) and an extract_test(VARCHAR)

3. Try to UPDATE extract_test with some text from the JSON field using JSON_EXTRACT_PATH_TEXT.

4. Use the errors from that to spot common characters which are screwing up the JSON. If I'm importing from web log data, I might find ???? or something similar

5. Use UPDATE table SET valid_json = false for JSON fields with that value

6. Finally, change the json fields in my original table using UPDATE c SET json_field = NULL FROM fixup_table f WHERE original_table.id = f.id AND f.valid_json = FALSE

It's still manual, but far quicker than fixing line by line on a big table, and by using the right DISTKEY/SORTKEY on your fixup table you can make the queries run quickly.

Redshift now supports passing a boolean argument that allows you to consider invalid JSON as null

select json_extract_path_text('invalid', 'path', true)

returns null

https://docs.aws.amazon.com/redshift/latest/dg/JSON_EXTRACT_PATH_TEXT.html

You can use the following function:

CREATE OR REPLACE FUNCTION isValidJSONv2(i varchar(MAX)) RETURNS int stable AS $CODE$
import json
import sys
try:
    if i is None:
        return 0
    json_object = json.loads(i)
    return 1
except:
    return 0
$CODE$ language plpythonu;

The problem still that if you still use the json parsing functions in the select, the error is still thrown. You would have to filter the valid from the unvalid jsons in different tables. I have posted the issue here: https://forums.aws.amazon.com/thread.jspa?threadID=232468

@Puneet's answer looked promising but it still failed when getting tripped up on "null" despite pre-processing the data in Python using json.dumps(). I ended up doing this:

,case 
    when context like '{"symptomId": null%' then null 
    when context like '{"symptomId%' then cast(json_extract_path_text(context, 'symptomId', true) as int) 
    else null
end as symptom_id

My concern is that some other pattern will emerge that also breaks. Maybe then the bool flag of "true" will catch such a scenario.