Parsing jsonb data in Postgresql Returns Null

Question

I'm trying to get the total_amount field in a jsonb formatted string in a table called "data_table".

I have tried this:

SELECT id, service_groups->>'total_amount' as pricing FROM data_table WHERE id = 'ef5d5366-f049-4d88-8f03-1f7c0faeeb8d'

It returns null for pricing. However in the original string it is actually 8.5:

[{"label": "instant", "currency": "SGD", "route_id": "cc20991a-36bc-4ebd-ade8-f14f1a38b0d0", "selected": true, "tax_amount": 0.0, "cutoff_time": "23:59", "gross_amount": 8.5, "total_amount": 8.5, "organizations": [{"id": "f894897d-8412-49c9-8443-48f1baca1aaa", "name": "Teleportal", "price": 6.0, "weight": 1.0, "tier_id": "2517c57e-99ad-4e75-bdfc-5e6439884b68", "services": ["last-mile"], "min_price": 8.0, "base_price": 0.0, "tax_amount": 0.0, "cutoff_time": "23:59", "gross_amount": 8.0, "total_amount": 8.0, "base_min_price": 0.0, "organization_id": "d25dbd90-bba2-4453-91c8-f2dc592b3da5", "estimated_duration": 1.0}], "discount_amount": 0.0, "insurance_amount": 0.0, "estimated_duration": 1.0, "estimated_delivery_on": "2021-02-03", "service_provider_gross_amount": 8.0, "service_provider_total_amount": 8.0, "service_provider_discount_amount": 0.0}]

What did I do wrong?

S-Man · Accepted Answer · 2021-02-03 13:58:28Z

2

You need to adress the first array element with -> 0 since your data is embedded into an array:

demo:db<>fiddle

SELECT data -> 0 ->> 'total_amount'

answered Feb 3, 2021 at 13:58

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parsing jsonb data in Postgresql Returns Null

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