Skip to content
Advertisement

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

I get

so in pretty formatting, the content of column data is

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

which returns

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

but I get

what am I doing wrong?

Advertisement

Answer

The problem in the query

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

or either

More info on JSON Functions and Operators can be find here

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement