>Doing regex date validation in a json derived from a string in an sql dialect without function-definitions is horrendous.

Doing regex is horrendous, but doing it on SQL in a modern database is no more difficult than in a full-fledged programming language. Most modern DBs have strong JSON support and built-in regex functions

1. JSON_CAST/JSON_PARSE your data

2. REGEXP_EXTRACT() on the result, here's several date validator regex from a SO post (https://stackoverflow.com/questions/15491894/regex-to-valida...)

And that's it. In fact in many cases it's probably faster to do it natively in SQL than to export it to python or R and parse there.