Query jsonb fields in the postgres SQL

Question

I'm trying fetch the maximum date from the jsonb column.

I'm getting this below error..

------------------------------------
id  |       obj_value(jsonb) 
------------------------------------
1   | {"value": [{"date": "2020-12-2020"}]} 
------------------------------------
2   | {"value": [{"date": "2020-12-2019"}]} 
---------------------------------
3   | {"value": [{"date": "2020-12-2018"}]} 
---------------------------------

here is the SQL query

SELECT max(obj_value->'value'->0->'effectiveDate')
FROM public.tbl_data  
where obj_value->'value'->0->>'effectiveDate' <= TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD')

I'm getting this below error..

ERROR:  function max(jsonb) does not exist
LINE 1: SELECT max(obj_value->'value'->0->'effectiveDate')
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 8

Use ->> instead of ->, just like you did in the WHERE condition — Frank Heikens
– Frank Heikens, Commented Jan 24, 2021 at 14:29

AlexElin · Accepted Answer · 2021-10-08 21:42:51Z

1

-> operator returns JSONB type,
->> retuns TEXT type.
You should use ->> and then cast to a type you need

answered Oct 8, 2021 at 21:42

AlexElin

1,7971 gold badge19 silver badges29 bronze badges

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

Collectives™ on Stack Overflow

Query jsonb fields in the postgres SQL

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