SQL get the value of a nested key in a jsonb field

Let’s suppose I have a table my_table with a field named data, of type jsonb, which thus contains a json data structure.

let’s suppose that if I run

select id, data from my_table where id=10;

I get

 id |                                     data
------------------------------------------------------------------------------------------
 10 | { 
    |"key_1": "value_1" ,
    |"key_2": ["value_list_element_1", "value_list_element_2", "value_list_element_3" ],
    |"key_3": {
    |          "key_3_1": "value_3_1", 
    |          "key_3_2": {"key_3_2_1": "value_3_2_1", "key_3_2_2": "value_3_2_2"}, 
    |          "key_3_3": "value_3_3" 
    |          }
    | }

so in pretty formatting, the content of column data is

{
  "key_1": "value_1",
  "key_2": [
    "value_list_element_1",
    "value_list_element_2",
    "value_list_element_3"
  ],
  "key_3": {
    "key_3_1": "value_3_1",
    "key_3_2": {
      "key_3_2_1": "value_3_2_1",
      "key_3_2_2": "value_3_2_2"
    },
    "key_3_3": "value_3_3"
  }
}

I know that If I want to get directly in a column the value of a key (of “level 1”) of the json, I can do it with the ->> operator.

For example, if I want to get the value of key_2, what I do is

select id, data->>'key_2' alias_for_key_2 from my_table where id=10;

which returns

 id |                                alias_for_key_2
------------------------------------------------------------------------------------------
 10 |["value_list_element_1", "value_list_element_2", "value_list_element_3" ]

Now let’s suppose I want to get the value of key_3_2_1, that is value_3_2_1.

How can I do it?

I have tryed with

select id, data->>'key_3'->>'key_3_2'->>'key_3_2_1' alias_for_key_3_2_1 from my_table where id=10;

but I get

select id, data->>'key_3'->>'key_3_2'->>'key_3_2_1' alias_for_key_3_2_1 from my_table where id=10;
                         ^
HINT:  No operators found with name and argument types provided. Types may need to be converted explicitly.

what am I doing wrong?

Answer

The problem in the query

select id, data->>'key_3'->>'key_3_2'->>'key_3_2_1' alias_for_key_3_2_1   --this is wrong!
from my_table 
where id=10; 

was that by using the ->> operand I was turning a json to a string, so that with the next ->> operand I was trying to get a json key object key_3_2 out of a string object, which makes no sense.

Thus one has to use the -> operand, which does not convert json into string, until one gets to the “final” key.

so the query I was looking for was

select id, data->'key_3'->'key_3_2'->>'key_3_2_1' alias_for_key_3_2_1  --final ->> : this gets the value of 'key_3_2_1' as string
from my_table 
where id=10;  

or either

select id, data->'key_3'->'key_3_2'->'key_3_2_1' alias_for_key_3_2_1  --final -> : this gets the value of 'key_3_2_1' as json / jsonb
from my_table 
where id=10;  

More info on JSON Functions and Operators can be find here