How to select data from jsonb column?

Question

I have got one column with type jsonb. The data in this column looks as below

{
    "random_number1":
    {
        "random_number2":
        {
            "Param1": 2,
            "Param2": 0,
            "Param3": 0,
            "Param4": 6,
            "Param5": 3
        }
    }
}

How to write select for this column if I want f.e. all rows where "Param3"=6 ? I tried something like that

SELECT * FROM table WHERE column->'Param3' @> '6'::jsonb;

klin klin · Accepted Answer · 2017-08-08T09:02:03

It depends on your expectations.

Get the value of a specified path:

select *
from my_table
where my_col->'random_number1'->'random_number2'->>'Param3' = '6'

Get the value of the key Param3 of any object on the third level:

select t.*
from my_table t,
jsonb_each(my_col) as value1(key1, value1),
jsonb_each(value1) as value2(key2, value2)
where jsonb_typeof(my_col) = 'object'
and jsonb_typeof(value1) = 'object'
and value2->>'Param3' = '6';

In the second case you may want to use distinct as the query may yield duplicated rows.

How to select data from jsonb column?

1 Answers