Standardize date of birth in JSON in postgres

Question

I have a mix of date of births in my database. Some are of the format yyyyy-mm-dd and some are of form mm:dd:yyyy. I am trying to calculate age and that makes it difficult for me to do date. How can i standardize the dates?

SELECT date_of_birth 
  FROM ( SELECT attrs::JSON->'info'->>'date_of_birth' 
           FROM users ) AS date_of_birth;

date_of_birth      
--------------
 (2000-11-03)
 (2000-06-11)
 (2000-05-31)
 (2008-11-26)
 (2007-11-09)
 (2020-03-26)
 (2018-06-30)
 (02:21:2020)

 (2007-10-09)

There are only a couple of mm:dd:yyyy date of births. I tried updating the field in the JSON by

UPDATE users 
   SET attrs::JSON->'info'->>'date_of_birth'='02-21-2020' 
 WHERE attrs::JSON->'info'->>'date_of_birth'='02:21:2020'

but its erroring out

ERROR: syntax error at or near "::"

There is, a very long, answer here ( https://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype ) If that does not answer your question, then Please add some sample data and your table structure. (like is explained in [mre]) — Luuk, Feb 27 '21 at 18:07
This is a good reason to have a properly normalized database, e.g. with a `date_of_birth` table attribute of type `date`. — Zack, Feb 27 '21 at 21:33

Barbaros Özhan · Accepted Answer · 2021-02-27T21:27:29.137

You can convert the data type of attrs from JSON to JSONB

ALTER TABLE users ALTER COLUMN attrs 
  SET DATA TYPE JSONB USING attrs::JSONB

Then, update the related data(which contains semi-colon within the date_of_birth data) by using JSONB_SET() function such as

UPDATE users
   SET attrs = JSONB_SET( attrs, '{info,date_of_birth}', 
                          ('"'||TO_DATE(
                                  attrs::JSONB->'info'->>'date_of_birth', 'mm:dd:yyyy'
                                  )::TEXT||'"')::JSONB 
                        )
  WHERE POSITION( ':' IN attrs::JSON->'info'->>'date_of_birth'::TEXT ) > 0

Demo

Standardize date of birth in JSON in postgres

1 Answers1