CODEWITHSUNDEEP
postgresqljsonb
Maksym Rybalkin
Maksym Rybalkin

Reputation: 533

Select json value frob jsonb column in postgres

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?

Upvotes: 0

Views: 20

Answers (1)

user330315
user330315

Reputation:

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}]}');

Online example

Upvotes: 2

Related Questions