Postgres JSONB values are all strings

Question

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?

This was the proper question, I had that coming. I will, cheers :) — Ray Toal, Feb 20 '23 at 01:36

score 1 · Accepted Answer · answered Feb 20 '23 at 01:31

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';

Postgres JSONB values are all strings

1 Answers1