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?

max taldykin · Accepted Answer · 2014-07-24 21:40:48Z

208

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;

answered Jul 24, 2014 at 21:40

max taldykin

13k5 gold badges49 silver badges69 bronze badges

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

3 Comments

Edgar Ortega Over a year ago

Don't forget to review the full list of JSON operators supported by PG postgresql.org/docs/current/static/functions-json.html

mga Over a year ago

what if you needed a list of all the breeds? is there support for something like select person->'dogs'->*->'breed'

max taldykin Over a year ago

@mga, see json_array_elements function. select dog->'breed' from people p, json_array_elements(p->'dogs') dog

Collectives™ on Stack Overflow

PostgreSQL Nested JSON Querying

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related