How do you convert text column data with Ruby JSON format ("key" => "value")-CodePudding

I have data in the comment column of the payments table. The data is stored as plain text in the following format:

{"foo"=>"bar"}

I need to query the value of the specific "foo" key and tried the following:

select comment::json -> 'foo' from payments

but because the data stored is not in JSON format I get the following error:

invalid input syntax for type json DETAIL: Token "=" is invalid. CONTEXT: JSON data, line 1: {"foo"=>"bar"}

which refers to the => that Ruby uses for Hashes.

Is there a way to convert the text data to JSON data on-the-fly so I can then access the specific keys I need?

CodePudding user response：

You can replace the => with a : to make that example a valid JSON value:

replace(comment, '=>', ':')::jsonb ->> 'foo'

CodePudding user response：

It sounds like the data is technically valid ruby which means we can do something a bit clever.

require 'json'
def parse_data(data_string)
  eval(data_string).to_json
end

Should do the trick so long as the data is trusted.