'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