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
– Ray Toal, Commented Feb 20, 2023 at 1:36

Bergi · Accepted Answer · 2023-02-20 01:31:03Z

1

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

answered Feb 20, 2023 at 1:31

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

Collectives™ on Stack Overflow

Postgres JSONB values are all strings

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related