postgresql jsonb contains key:value on second level

Question

Is there a possibility in postgresql to query for a key:value on the second level

for example the jsonb field of a row looks like this:

{
   "something": {
      "v_id": "5544d28431f19", 
      "value": "xyz"
   }, 
   "something_else": {
      "v_id": "5544d28431feb", 
      "value": "abc"
   }
}

I want to query for this row using the v_id value like:

  SELECT id, jsonb_field
  FROM table_1
  WHERE jsonb_field @> '{{"v_id": "5544d28431feb"}}'
  ;

However, this query is not valid. How to achieve such a query?

Edit:

According to @CraigRinger's comment:

The point here is that I don't know the top level key, I want to say "for any object, is there an inner object that has the following key with the following value".

Answer 1

You can use a lateral join to call jsonb_each for each row in your table. The function jsonb_each turns each node into a row with two columns called key and value:

select  value
from    table_1
cross join lateral
        jsonb_each(jsonb_field) sub
where   value @> '{"v_id": "5544d28431feb"}';

Full example (no SQL Fiddle support for Postgres 9.4 yet):

create table table_1 (id int primary key, jsonb_field jsonb);
insert into table_1 (id, jsonb_field) values (42, '{
   "something": {
      "v_id": "5544d28431f19", 
      "value": "xyz"          
   },                                       
   "something_else": {
      "v_id": "5544d28431feb", 
      "value": "abc"
   }
}');

select  value
from    table_1
cross join lateral
        jsonb_each(jsonb_field) t2
where   value @> '{"v_id": "5544d28431feb"}';

This prints:

                   value                   
-------------------------------------------
 {"v_id": "5544d28431feb", "value": "abc"}