can not retrieve json object value postgresql 9.6

Question

DO
$$
DECLARE
array_ json;
category_name text;
category_ json:='{"Maincategory":"Vehicle","Categoryname":"Car"}'::json;
BEGIN
category_name:=(SELECT category_  ->'Maincategory'::text) ;
array_:=(SELECT elements -> category_name FROM webuser.footable WHERE id=somenumber)::json;
raise notice '%',array_ ;
END
$$;


                             elements                       
                              (json)                    
|-------------------------------------------------------------------------------------|
|{"Vehicle": [{"Car": 3}, {"Truck": 1}], "Office": [{"Printer": 3}, {"Desk": 8}]}     |

I have elements column and i want to retrieve vehicle array which is ([{"Car": 3}, {"Truck": 1}] ) that will be stored in array_ variable. I get array_ always null. What is wrong with this code?

Vao Tsun · Accepted Answer · 2016-12-05 16:31:02Z

1

category_ ->'Maincategory'::text will return "Vehicle", while you need the value of the filed...

change (SELECT category_ ->'Maincategory'::text)

to

(SELECT category_ ->>'Maincategory'::text), like here:

b=# DO
b-# $$
b$# DECLARE
b$# array_ json;
b$# category_name text;
b$# category_ json:='{"Maincategory":"Vehicle","Categoryname":"Car"}'::json;
b$# elements  json:= '{"Vehicle": [{"Car": 3}, {"Truck": 1}], "Office": [{"Printer": 3}, {"Desk": 8}]}';
b$# BEGIN
b$# category_name:=(SELECT category_ ->>'Maincategory'::text) ;
b$# raise info '%',(select elements->category_name);
b$# --array_:=(SELECT elements -> category_name FROM webuser.footable WHERE id=somenumber)::json;
b$# --raise notice '%',array_ ;
b$# END
b$# $$;
INFO:  [{"Car": 3}, {"Truck": 1}]
DO

edited Dec 5, 2016 at 16:31

answered Dec 5, 2016 at 16:18

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

2 Comments

sommeguyy Over a year ago

that works but if i use a variable(in that case : category_name) instead of 'Vehicle' than it doesnt work.

Vao Tsun Over a year ago

@sommeguyy I updated the answer - mind -> is different from ->>

Collectives™ on Stack Overflow

can not retrieve json object value postgresql 9.6

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related