Troubles extract data from json with PostgreSQL

Question

I have a text column like this:

{
   "addFee":0,
   "addFeeTotal":0,
   "addFeeType":0,
   "addFeeUsers":0,
   "addFeeVat":0,
   "addFeeYears":0,
   "priceTotal":0,
   "unit1":"db",
   "unit1Value":5,
   "unit2":null,
   "unit2Value":0
}

I'd like list the priceTotal values, but

I got "cannot extract elements from a scalar" for this:

select p 
from web.order_details, 
   jsonb_array_elements_text(calculatedproductprice::jsonb -> 'priceTotal') as p

And got "function jsonb_array_elements_text(text) does not exist" for this:

select js ->> 'priceTotal' as p 
from web.order_details, 
   jsonb_array_elements_text(calculatedproductprice) as js

I ran out of ideas, but maybe this is not a valid json data?

user330315 · Accepted Answer · 2020-06-03 09:06:57Z

1

Well, your JSON isn't an array, so obviously jsonb_array_elements isn't the right function to use.

As the key you are interested in, is a top-level key, you don't need anything else than just the ->> operator:

select calculatedproductprice::jsonb -> 'priceTotal' as price_total
from web.order_details

answered Jun 3, 2020 at 9:06

user330315

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

Collectives™ on Stack Overflow

Troubles extract data from json with PostgreSQL

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