Select json value frob jsonb column in postgres

Question

Here is my inserting request to my postgres table:

insert into pay_process (created, execution_time, updated, bundle_id, processing_status, xxi_balance)
values (current_timestamp, current_timestamp, current_timestamp, 1, 'SUCCESS',
        '"{\"balances\": [{\"bs_day\": \"2021-02-12\", \"balance\": 1167.18}, {\"bs_day\": \"2021-02-13\", \"balance\": 4796.45}]}"');

xxi_balance has jsonb type. I need to select my balances array, I use the request for that:

select xxi_balance::json->'balances' as balances from pay_process;

But it returns nothing. What is my mistake?

user330315 · Accepted Answer · 2021-03-18 11:52:03Z

2

You are storing one large JSON scalar, not a nested JSON object. You need to remove the double quotes surrounding the whole value and the escaped double quotes inside the value:

insert into pay_process 
 (created, execution_time, updated, bundle_id, processing_status, xxi_balance)
values 
  (current_timestamp, current_timestamp, current_timestamp, 1, 'SUCCESS',
   '{"balances": [{"bs_day": "2021-02-12", "balance": 1167.18}, {"bs_day": "2021-02-13", "balance": 4796.45}]}');

Collectives™ on Stack Overflow

Select json value frob jsonb column in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related