Somehow populating a database with a JSONB column ended up with every value in the column being a JSONB string instead of an object.
=> select specifications from checklist_item;
specifications
---------------------
"{}"
"{}"
"{\"x\": 2, \"y\": \"z\"}"
Is it possible to update, in a single statement, each of these values to JSONB objects as opposed to strings?
I tried to_jsonb(specifications) but that did not parse as expected. I’ve gone over documentation but all the examples seem to show ways to manipulate data that is already a jsonb array or a jsonb object but not a plain string.
I can write a script and do the parsing in Python, but there certainly must be a nice way to do with in a single update command with a json function that I simply cannot find at the moment. Is there such a json function or operator that will "parse" my bad data?
>Solution :
to_jsonb(specifications) does to_jsonb(specifications::text), which just gets the JSON text with the string literal as text. What you need is to get the value of the JSON string literal, then cast that to jsonb:
UPDATE checklist_item
SET specifications = (specifications #>> '{}')::jsonb
-- or … = to_jsonb(specifications #>> '{}')
WHERE jsonb_typeof(specifications) = 'string';