title: How Do I Access Nested JSON in Postgresql 9.4?
body: <p>I'm trying to access a nested jsonb field in Postgresql 9.4. But I'm having difficulty retrieving the record based on its nested jsonb values. Has anyone had success with this? </p>
  
  <ul>
  <li><p>First, I created the table:</p>
  
  <pre><code>CREATE TABLE Meal (
      id INT
    , recipe JSONB
  );
  </code></pre></li>
  <li><p>Second, I inserted the following record into dbo.Meal:
  (I ran the json through jsonlint.com, and it came back valid)</p>
  
  <pre><code>INSERT INTO Meal (id, recipe)
  VALUES (
  1,
  '{
    "meal": [{
    "calories" : 900,
    "serves" : [{"min": 2, "max": 4}],
    "fruit" : [{"id": 1, "qty": 2}, {"id": 4, "qty": 3}],
    "veggie" : [{"id": 4, "qty": 1}, {"id": 2, "qty": 10}]
   }]
  }');
  </code></pre></li>
  <li><p>Third, I tried the following queries to retrieve this record based on its calorie count (none of which are working):</p></li>
  </ul>
  
  <p>These return 0 records:</p>
  
  <pre><code>SELECT * FROM Meal ...
  
  WHERE recipe::json#&gt;&gt;'{meal, calories}' = '900';
  WHERE recipe::json-&gt;&gt;'{meal, calories}' = '900';
  WHERE recipe::json-&gt;&gt;'meal[calories]' = '900';
  WHERE recipe::json-&gt;&gt;'{meal[calories]}' = '900';
  WHERE recipe::json#&gt;&gt;'{meal[calories]}' = '900';
  WHERE recipe::json#&gt;&gt;'{meal.calories}' = '900';
  WHERE recipe::json-&gt;&gt;'{meal.calories}' = '900';
  
  WHERE recipe::jsonb#&gt;&gt;'{meal, calories}' = '900';
  WHERE recipe::jsonb-&gt;&gt;'{meal, calories}' = '900';
  WHERE recipe::jsonb#&gt;&gt;'{meal[calories]}' = '900';
  WHERE recipe::jsonb-&gt;&gt;'{meal[calories]}' = '900';
  WHERE recipe::jsonb-&gt;&gt;'meal[calories]' = '900';
  WHERE recipe::jsonb#&gt;'{meal, calories}' = '900';
  WHERE recipe::jsonb-&gt;'{meal, calories}' = '900';
  WHERE recipe::jsonb-&gt;'meal[calories]' = '900';
  WHERE recipe::jsonb#&gt;'{meal[calories]}' = '900';
  WHERE recipe::jsonb-&gt;'{meal[calories]}' = '900';
  WHERE recipe::jsonb#&gt;&gt;'{meal.calories}' = '900';
  WHERE recipe::jsonb#&gt;'{meal.calories}' = '900';
  WHERE recipe::jsonb-&gt;&gt;'{meal.calories}' = '900';
  WHERE recipe::jsonb-&gt;'{meal.calories}' = '900';
  </code></pre>
  
  <p>These result in failure (incorrect syntax):</p>
  
  <pre><code>SELECT * FROM Meal ...
  
  WHERE recipe::json#&gt;'{meal, calories}' = '900';
  WHERE recipe::json-&gt;'{meal, calories}' = '900';
  WHERE recipe::json#&gt;&gt;'meal[calories]' = '900';
  WHERE recipe::json#&gt;'meal[calories]' = '900';
  WHERE recipe::json-&gt;'meal[calories]' = '900';
  WHERE recipe::json#&gt;'{meal[calories]}' = '900';
  WHERE recipe::json-&gt;'{meal[calories]}' = '900';
  WHERE recipe::json#&gt;'{meal.calories}' = '900';
  WHERE recipe::json-&gt;'{meal.calories}' = '900';
  
  WHERE recipe::jsonb#&gt;&gt;'meal[calories]' = '900';
  WHERE recipe::jsonb#&gt;'meal[calories]' = '900';
  </code></pre>
  
  <p>If you have any suggestions I would greatly appreciate hearing them.</p>