PostgreSQL Convert Rails serialized YAML to JSON query

serialized_conversion.sql

-- replace table_name with the name of your database table, e.g. users
-- replace data with the serialized field in the table, e.g. nicknames
-- output generates serialized data that decrypts to a Ruby array.
-- data with quotes, hyphens, or spaces will have issues with this query.

-- preview the YAML to JSON to confirm the conversion is working.
SELECT data, REPLACE(REPLACE(REPLACE(REPLACE(REGEXP_REPLACE(REPLACE(REPLACE(REPLACE(data, 
	' ', ''),
	'...', ''),
	'---', '["'),
	E'[\\n\\r]+', '"', 'g'),
	'''''', ''),
	'-', ',"'),
	'"",', ''),
	'""', '')
	|| ']' FROM table_name;

-- convert the YAML to JSON. (destructive)
UPDATE table_name SET data = 
REPLACE(REPLACE(REPLACE(REPLACE(REGEXP_REPLACE(REPLACE(REPLACE(REPLACE(data, 
	' ', ''),
	'...', ''),
	'---', '["'),
	E'[\\n\\r]+', '"', 'g'),
	'''''', ''),
	'-', ',"'),
	'"",', ''),
	'""', '')
	|| ']';