Skip to content

Instantly share code, notes, and snippets.

@tabiodun
Forked from statique/serialized_conversion.sql
Created December 13, 2018 00:41
Show Gist options
  • Save tabiodun/fd31939723a6d44f0042ea4ae753500f to your computer and use it in GitHub Desktop.
Save tabiodun/fd31939723a6d44f0042ea4ae753500f to your computer and use it in GitHub Desktop.
PostgreSQL Convert Rails serialized YAML to JSON query
-- 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'),
'''''', ''),
'-', ',"'),
'"",', ''),
'""', '')
|| ']';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment