CODEWITHSUNDEEP
jsonpostgresql
STO
STO

Reputation: 133

Troubles extract data from json with PostgreSQL

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?

Upvotes: 0

Views: 93

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions