Home > Enterprise >  How can i replace JSON values in Redshift?
How can i replace JSON values in Redshift?

Time:03-02

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 ('           
  • Related