PostgreSQL Nested JSON Querying

Question

On PostgreSQL 9.3.4, I have a JSON type column called "person" and the data stored in it is in the format {dogs: [{breed: <>, name: <>}, {breed: <>, name: <>}]}. I want to retrieve the breed of dog at index 0. Here are the two queries I ran:

Doesn't work

db=> select person->'dogs'->>0->'breed' from people where id = 77; ERROR:  operator does not exist: text -> unknown LINE 1: select person->'dogs'->>0->'bree...                                  ^ HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts. 

Works

select (person->'dogs'->>0)::json->'breed' from es_config_app_solutiondraft where id = 77;  ?column? -----------  "westie" (1 row) 

Why is the type casting necessary? Isn't it inefficient? Am I doing something wrong or is this necessary for postgres JSON support?

Answer 1

This is because operator ->> gets JSON array element as text. You need a cast to convert its result back to JSON.

You can eliminate this redundant cast by using operator ->:

select person->'dogs'->0->'breed' from people where id = 77;