Accidentally stored string instead of object in Postgres JSONB column

Question

I just realized that the data being inserted into a JSONB column is a string like "{\"foo\": 3}" instead of an object like {"foo": 3}. I'm pretty sure the cause is due to setting the SQLAlchemy model's field with

reading.blob = blob_json

instead of

reading.blob = json.loads(blob_json)

I haven't been able to figure out a way to correct the existing data, though. The Postgres docs give examples of converting text to JSON, extracting array and object elements out of JSON, etc., but I can't find anything that shows how to select the text of a JSONB column that has only one string value.

Answer 1

Yes, just when I got this:

SELECT (('"{\"foo\":42}"'::JSONB)->>0)::JSONB;

Answer 2

Eureka! DeciBull on #postgres came up with

SELECT (json_build_array('"{\"Stuff.Hello\": 0, \"Stuff.Hello2\": 1000}"'::jsonb)->>0)::jsonb

{"Stuff.Hello": 0, "Stuff.Hello2": 1000}