Querying JSON (specific situation)

I am trying to query a specific example of JSON, see below:

The column name is “values”. If I use values -> 'members' I will get everything underneath. However, if I want the “gender” using values -> 'members' ->> 'gender' – nothing returns.

I believe this is because there is a “+” underneath members, so there is a field after “members” and before “gender” that I need to write in the query.

Any ideas?

Answer

You are defining your members as an array. Therefore (if you are using PostgreSQL), the correct query is:

postgres=# select (values->'members')->0->>'gender' from my_table;
 ?column? 
----------
 male
(1 row)

Disclosure: I work for EnterpriseDB (EDB)