Test string value as valid json content

I’ve a “log” table with a “log_body” column TEXT.
This table has some logs inside (about 3.000.000 records).
I’ve renamed this table to “log_old” and created a new one with “log” name and now “log_body” is of type jsonb.
I’m trying to insert data from “log_old” into new “log” table, but I’ve discovered that some “log_body” text is not a valid json data.
How can I filter invalid json data in the following statement:

insert into log (id, log_level, log_header, log_body)
select id, log_level, log_header, log_body::jsonb
from log_old
-- here I need something to filter data, like: where json_valid(log_old.log_body)

I don’t mind to loose few invalid records - they are invalid and has no use for me anyway.

I’ll appreciate your prompt help as always!

ER

Unfortunately there isn’t a predicate function to do this, or even check if a cast is valid in a where clause.

The closest you could get to this functionality is likely using a REGEX clause.

Something likeWHERE col LIKE '{%}'

1 Like

Thanks, but Json values are quite complex. I’m writing a Go program to migrate data.

Best regards,

ER.

As Richard says, that feature doesn’t currently exist.

However, I think it’s a good idea. I’ve opened this: New function that validates JSON - json_valid() · Issue #71453 · cockroachdb/cockroach · GitHub