Those are problems in MS Sql, certainly not in object relational DBs such as Postgres or Oracle.
And its rather sad that instead of embracing that we ended up with Json as poor man replacement for such advanced usages.
I guess non portability across DBs certainly doesnt help.
I tried showing that 10 years ago: https://github.com/ngs-doo/revenj/ but it just resulted in confusion.