1

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

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Madhusudan Pareek
  • 11
  • 2
  • Does this answer your question? [How to perform update operations on columns of type JSONB in Postgres 9.4](https://stackoverflow.com/questions/26703476/how-to-perform-update-operations-on-columns-of-type-jsonb-in-postgres-9-4) – Holger Just Jan 25 '22 at 18:05

1 Answers1

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

amit.s19
  • 193
  • 10