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.

I may have this figured out. Based on the table in the OP, the following 2 queries correctly return a record: (1) SELECT * FROM Meal WHERE recipe @> '{"meal":[{"calories":900}]}'::jsonb; (2) SELECT * FROM Meal WHERE recipe @> '{"meal":[{"calories":900}]}'::jsonb AND recipe @> '{"meal":[{"fruit":[{"id":4}]}]}'::jsonb; And the following query will NOT return a record (which is correct since meal.fruit.id = 40 does not exist): (3) SELECT * FROM Meal WHERE recipe @> '{"meal":[{"calories":900}]}'::jsonb AND recipe @> '{"meal":[{"fruit":[{"id":40}]}]}'::jsonb; Hope this helps! — bjones1831, May 18 '14 at 06:37
Asking a question about a new feature in a beta release that is out less than 72 hours is not going to yield many informed answers on SO, if any at all. Questions in this context are better asked at one of the postgresql mailing lists, -hackers comes to mind. — Patrick, May 18 '14 at 06:43

score 15 · Accepted Answer · answered May 18 '14 at 09:24

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.

I'm marking this as the answer since this works and... it also helps me with using jsonb values in comparisons (e.g. tweaking craig's query from " = 900 " to " > 900 " would not return a record. Which is correct... Thanks for the help! I've been trying to figure out the comparison thing for most of today. — bjones1831, May 18 '14 at 22:09
On PostgreSQL 9.5 you can use the @> operator as described on: http://stackoverflow.com/questions/28486192/postgresql-query-array-of-objects-in-jsonb-field — Andrés Torres Marroquín, Aug 29 '16 at 19:20

How Do I Access Nested JSON in Postgresql 9.4?

1 Answers1