how i can edit or update json data inside postgresql database

Question

i want to edit and update the data of "food_items", i want to add some objects inside it.

the json data which is stored inside json data is like below

[{"day": 0, "time": "6:00 AM", "calories": 0, "mealName": "Morning Snacks", "food_items": []}, {"day": 0, "time": "9:00 AM", "calories": 0, "mealName": "Breakfast", "food_items": []}, {"day": 0, "time": "12:00 PM", "calories": 0, "mealName": "Lunch", "food_items": []}, {"day": 0, "time": "6:00 PM", "calories": 0, "mealName": "Evening Snacks", "food_items": []}, {"day": 0, "time": "9:00 PM", "calories": 0, "mealName": "Dinner", "food_items": []}, {"day": 0, "time": "10:00 PM", "calories": 0, "mealName": "Other", "food_items": []}, {"day": 3, "time": "6:00 AM", "calories": 0, "mealName": "Morning Snacks", "food_items": []}, {"day": 3, "time": "9:00 AM", "calories": 0, "mealName": "Breakfast", "food_items": []}, {"day": 3, "time": "12:00 PM", "calories": 0, "mealName": "Lunch", "food_items": []}, {"day": 3, "time": "6:00 PM", "calories": 0, "mealName": "Evening Snacks", "food_items": []}, {"day": 3, "time": "9:00 PM", "calories": 0, "mealName": "Dinner", "food_items": []}, {"day": 3, "time": "10:00 PM", "calories": 0, "mealName": "Other", "food_items": []}, {"day": 4, "time": "6:00 AM", "calories": 0, "mealName": "Morning Snacks", "food_items": []}, {"day": 4, "time": "9:00 AM", "calories": 0, "mealName": "Breakfast", "food_items": []}, {"day": 4, "time": "12:00 PM", "calories": 0, "mealName": "Lunch", "food_items": []}, {"day": 4, "time": "6:00 PM", "calories": 0, "mealName": "Evening Snacks", "food_items": []}, {"day": 4, "time": "9:00 PM", "calories": 0, "mealName": "Dinner", "food_items": []}, {"day": 4, "time": "10:00 PM", "calories": 0, "mealName": "Other", "food_items": []}, {"day": 6, "time": "6:00 AM", "calories": 0, "mealName": "Morning Snacks", "food_items": []}, {"day": 6, "time": "9:00 AM", "calories": 0, "mealName": "Breakfast", "food_items": []}, {"day": 6, "time": "12:00 PM", "calories": 0, "mealName": "Lunch", "food_items": []}, {"day": 6, "time": "6:00 PM", "calories": 0, "mealName": "Evening Snacks", "food_items": []}, {"day": 6, "time": "9:00 PM", "calories": 0, "mealName": "Dinner", "food_items": []}, {"day": 6, "time": "10:00 PM", "calories": 0, "mealName": "Other", "food_items": []}]

i was using this query to get the data day wise from json

SELECT  jsonb_agg(distinct j)
        FROM table_name  t
        CROSS JOIN LATERAL jsonb_array_elements(t.di_item) j
        WHERE j->>'day' = 1 and t.di_cid =14`

and by this i was able to get the data and now i need a query by which i can update the data inside the json array which is food_items.

thanking you ..in advance

Answer 1

You can use jsonb_set method of postgresql. Follow this link for more information on how to use it : https://aaronbos.dev/posts/update-json-postgresql