I have a JSON array in my redshift table which has True/False/None
values. Now since JSON only accepts lower case values and don't accept None
, I want to convert those values into lower case true/false/null
values. Bearing in my mind my JSON array has some keys whose values also contain the name True.
Example :
[{"id": 20198, "name": "True Ventures", "path": "true_ventures", "type": "Fund", "lead": False}, {"id": 324746, "name": "XXX", "path": "XXX", "type": "Investor", "url": "XXX", "image": "XXX", "lead": False}]
[{"id": 20198, "name": "True Ventures", "path": "true_ventures", "type": "Fund", "lead": True}, {"id": 324746, "name": "XXX", "path": "XXX", "type": "Investor", "url": "XXX", "image": "XXX", "lead": True}]
[{"id": 20198, "name": "True Ventures", "path": "true_ventures", "type": "Fund", "lead": None}, {"id": 324746, "name": "XXX", "path": "XXX", "type": "Investor", "url": "XXX", "image": "XXX", "lead": None}]
Now, I want to replace the False/True/None
values occurrences across the entire JSON array wherever there is a True
, False
, None
values. (in this case for the lead key and not the name and path of the company). I am currently using
case
when column_name ilike ('%True%') then regexp_replace(replace(column_name, '\'', '"'), 'True', 'true')
when column_name ilike ('