I have this string (character varying) as a row value in Postgresql :
{'img_0': 'https://random.com/xxxxxx.jpg', 'img_1': 'https://random.com/yyyyyy.jpg', 'img_2': 'https://random.com/zzzzzz.jpg'}
I am trying to json_eact_text() it but can't figure out how to. I have tried to_jsonb() on it (working) and then jsonb_each(), but I have this error :
ERROR: cannot call jsonb_each on a non-object
My query :
WITH
test AS (
SELECT to_jsonb(value) as value FROM attribute_value WHERE id = 43918
)
SELECT jsonb_each(value) FROM test
Your text value is not valid JSON. JSON requires doublequotes (") to delimit strings.
This will work by doctoring your text provided that your data is consistently wrong:
with t (sometext) as (
values ($${'img_0': 'https://random.com/xxxxxx.jpg', 'img_1': 'https://random.com/yyyyyy.jpg', 'img_2': 'https://random.com/zzzzzz.jpg'}$$)
)
select jsonb_each_text(replace(sometext, '''', '"')::jsonb)
from t;
jsonb_each_text
---------------------------------------
(img_0,https://random.com/xxxxxx.jpg)
(img_1,https://random.com/yyyyyy.jpg)
(img_2,https://random.com/zzzzzz.jpg)
(3 rows)
To break this out into columns:
with t (sometext) as (
values ($${'img_0': 'https://random.com/xxxxxx.jpg', 'img_1': 'https://random.com/yyyyyy.jpg', 'img_2': 'https://random.com/zzzzzz.jpg'}$$)
)
select j.*
from t
cross join lateral jsonb_each_text(replace(sometext, '''', '"')::jsonb) as j;
key | value
-------+-------------------------------
img_0 | https://random.com/xxxxxx.jpg
img_1 | https://random.com/yyyyyy.jpg
img_2 | https://random.com/zzzzzz.jpg
(3 rows)