Postgresql string to jsonb_each_text()

Question

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

Mike Organek · Accepted Answer · 2020-08-04 20:40:52Z

1

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)

edited Aug 4, 2020 at 20:40

answered Aug 4, 2020 at 16:51

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

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

2 Comments

IndiaSke Over a year ago

I had the replace() wrong. Is it possible to have 'img_0' and 'random.com/xxxxxx.jpg' in two different columns ?

Mike Organek Over a year ago

@IndiaSke Certainly. Please see the update to the answer.

Collectives™ on Stack Overflow

Postgresql string to jsonb_each_text()

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related