How Do I Access Nested JSON in Postgresql 9.4?

Question

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?

• First, I created the table:

  CREATE TABLE Meal (
      id INT
    , recipe JSONB
  );
  

• Second, I inserted the following record into dbo.Meal: (I ran the json through jsonlint.com, and it came back valid)

  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}]
   }]
  }');
  

• Third, I tried the following queries to retrieve this record based on its calorie count (none of which are working):

These return 0 records:

SELECT * FROM Meal ...

WHERE recipe::json#>>'{meal, calories}' = '900';
WHERE recipe::json->>'{meal, calories}' = '900';
WHERE recipe::json->>'meal[calories]' = '900';
WHERE recipe::json->>'{meal[calories]}' = '900';
WHERE recipe::json#>>'{meal[calories]}' = '900';
WHERE recipe::json#>>'{meal.calories}' = '900';
WHERE recipe::json->>'{meal.calories}' = '900';

WHERE recipe::jsonb#>>'{meal, calories}' = '900';
WHERE recipe::jsonb->>'{meal, calories}' = '900';
WHERE recipe::jsonb#>>'{meal[calories]}' = '900';
WHERE recipe::jsonb->>'{meal[calories]}' = '900';
WHERE recipe::jsonb->>'meal[calories]' = '900';
WHERE recipe::jsonb#>'{meal, calories}' = '900';
WHERE recipe::jsonb->'{meal, calories}' = '900';
WHERE recipe::jsonb->'meal[calories]' = '900';
WHERE recipe::jsonb#>'{meal[calories]}' = '900';
WHERE recipe::jsonb->'{meal[calories]}' = '900';
WHERE recipe::jsonb#>>'{meal.calories}' = '900';
WHERE recipe::jsonb#>'{meal.calories}' = '900';
WHERE recipe::jsonb->>'{meal.calories}' = '900';
WHERE recipe::jsonb->'{meal.calories}' = '900';

These result in failure (incorrect syntax):

SELECT * FROM Meal ...

WHERE recipe::json#>'{meal, calories}' = '900';
WHERE recipe::json->'{meal, calories}' = '900';
WHERE recipe::json#>>'meal[calories]' = '900';
WHERE recipe::json#>'meal[calories]' = '900';
WHERE recipe::json->'meal[calories]' = '900';
WHERE recipe::json#>'{meal[calories]}' = '900';
WHERE recipe::json->'{meal[calories]}' = '900';
WHERE recipe::json#>'{meal.calories}' = '900';
WHERE recipe::json->'{meal.calories}' = '900';

WHERE recipe::jsonb#>>'meal[calories]' = '900';
WHERE recipe::jsonb#>'meal[calories]' = '900';

If you have any suggestions I would greatly appreciate hearing them.

Answer 1

To select the meal:

select * from meal where recipe #>> '{meal,0,calories}' = '900';

If you want to find those entries within the array meal, you have to iterate the array to examine each key. There's no wildcard array index or object name placeholder - you can't write {meal,*,calories}. Not yet anyway; json functionality continues to improve.

Here's how I'd do it:

select meal.id, recipe_entry 
from meal,
lateral jsonb_array_elements(recipe -> 'meal') recipe_entry 
where CAST(recipe_entry ->> 'calories' AS integer) = 900;

Some possible future enhancements to json functionality would make this a lot easier. A wildcard-capable path search function that could return a set would be very helpful - probably as enhancements to json_extract_path. Perhaps in 9.5 if someone proves eager. The other thing that'd really help would be a conversion function or cast for json scalars, so we could write recipe_entry -> 'calories' = to_json(900) and get Javascript-like equality comparison semantics, rather than relying on the above cast.