Postgres replace operation when finds None-CodePudding

I have a field that I want to cast as json but the keys are single quoted instead of double quoted:

{'id': 12249, 'value': 'any string'}
{'id': 12232, 'value': 'other thing'}

I am using this:

select replace(column,'''','"')::jsonb as columnj from table

The code above works fine until it finds a row with None like this:

{'id': None, 'value': None}

How can I fix this situation?

CodePudding user response：

If you still want to keep the None-valued rows, you can add quotes to None values using a nested REPLACE:

SELECT REPLACE(REPLACE(json_str,'''','"'), 
               'None', 
               '"None"')                   ::JSONB AS columnj 
FROM tab

If instead you don't want to include in the json those rows, which have the None value, then you can just filter them out:

SELECT REPLACE(json_str,'''','"')::JSONB AS columnj 
FROM tab
WHERE json_str NOT LIKE '%None%';

Check the demo of both solutions here.