Tengo esta cadena (caracter variable) como un valor de fila en Postgresql:
{'img_0': 'https://random.com/xxxxxx.jpg', 'img_1': 'https://random.com/yyyyyy.jpg', 'img_2': 'https://random.com/zzzzzz.jpg'}Estoy tratando de json_eact_text() pero no sé cómo hacerlo. He intentado to_jsonb() en él (funcionando) y luego jsonb_each(), pero tengo este error:
ERROR: cannot call jsonb_each on a non-objectMi consulta:
WITH test AS ( SELECT to_jsonb(value) as value FROM attribute_value WHERE id = 43918 ) SELECT jsonb_each(value) FROM testSu valor de texto no es JSON válido. JSON requiere comillas dobles ( " ) para delimitar cadenas.
Esto funcionará modificando su texto siempre que sus datos sean incorrectos constantemente:
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)Para dividir esto en columnas:
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)