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.

byMike · Accepted Answer · 2016-02-12 22:30:49Z

5

Yes, just when I got this:

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

answered Feb 12, 2016 at 22:30

byMike

3193 silver badges9 bronze badges

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

Comments

user2657283 · Accepted Answer · 2016-02-12 22:16:12Z

4

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}

answered Feb 12, 2016 at 22:16

user2657283

736 bronze badges

Collectives™ on Stack Overflow

Accidentally stored string instead of object in Postgres JSONB column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related