Postgres : Unable to extract data from a bytea column which stores json array data-CodePudding

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.

CodePudding user response：

You are making your life unnecessary hard by storing JSON values in a bytea column. Just because this is the recommended way in Oracle, doesn't mean this is a good choice for Postgres.

The correct solution is to change that column to jsonb. You will have to have a DBMS specific layer in your application anyway as the actual functions and operators you are using are very different.

Having said that, you can get away with this awful choice by using the convert_from() method:

select e.name, element1.*
from employee e
 left join lateral jsonb_array_elements(convert_from(PAYMENT, 'UTF-8')::jsonb) element1 on true;

I also think you should change your INSERT statement to do an explicit conversion from text to bytea so that you can be sure the correct encoding is used:

insert into employee (payment, name)
values (convert_to('[{...}]', 'UTF-8'),'Tom');

But again: the only correct solution is to change that column to jsonb (or least json)