👀 Views: 0 💬 Answers: 1 📅 Created: 2025-06-12

postgresql jsonb database SQL

This might be a silly question, but I'm currently working with a PostgreSQL 12 database where I need to update a nested field within a JSONB column. The structure of my JSONB data looks something like this: ```json { "user": { "name": "John", "settings": { "notifications": true, "theme": "dark" } } } ``` I want to update the 'theme' field under 'settings' to 'light'. I'm using the `jsonb_set` function as follows: ```sql UPDATE users SET data = jsonb_set(data, '{user,settings,theme}', '"light"') WHERE id = 1; ``` However, when I run this query, I receive the behavior: `behavior: invalid input syntax for type json`. I've tried surrounding the string with different quotes and escaping, but nothing seems to work. I've also attempted using `jsonb_set` in combination with `to_jsonb`, like this: ```sql UPDATE users SET data = jsonb_set(data, '{user,settings,theme}', to_jsonb('light'::text)) WHERE id = 1; ``` This still results in the same behavior. I’m not sure if the question lies in how I'm constructing the path or in the type of data I'm trying to set. Does anyone have insights on how to properly update nested JSONB fields in PostgreSQL without working with this behavior? Any ideas what could be causing this? Thanks for taking the time to read this!