'Postgres : Unable to extract data from a bytea column which stores json array data
I'm trying to extract data from a bytea column which stores JSON data in Postgres 11.9 version.
However, the my code is throwing an error:
ERROR: invalid input syntax for type json DETAIL: Token "" is invalid. CONTEXT: JSON data, line 1: ...
Here is the sample data:
create table EMPLOYEE (PAYMENT bytea,NAME character varying);
insert into EMPLOYEE
values ('[{"totalCode":{"code":"EMPLOYER_TAXES"},"totalValue":{"amount":122.5,"currencyCode":"USD"}},{"totalCode":{"code":"OTHER_PAYMENTS"},"totalValue":{"amount":0.0,"currencyCode":"USD"}},{"totalCode":{"code":"GROSS_PAY"},"totalValue":{"amount":1000.0,"currencyCode":"USD"}},{"totalCode":{"code":"TOTAL_HOURS"},"totalValue":{"amount":40.0}}]'::bytea,'Tom')
;
Here is my query:
SELECT *
FROM EMPLOYEE left outer join lateral
jsonb_array_elements(PAYMENT::text::jsonb) element1 on true ;
Please help me in accessing data from this array. Data is always JSON in format. There was a restriction to use bytea for this column.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
