Postgres replace operation when finds None

Question

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?

lemon · Accepted Answer · 2022-06-05 13:34:00Z

1

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.

answered Jun 5, 2022 at 13:34

lemon

15.6k6 gold badges23 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1