JSON data in postgres

While playing around with open-webui I managed to mess up the configuration more than once. In some situations this even led to an unresponsive system with even the configuration pages (to revert the latest changes) was no longer accessible.

So I went for the database (postgres in my case) to revert those settings only to find, that the whole configuration is stored in one big JSON blob.

To my surprise I found that postgres does indeed support JSON data type, so with a little help of Google and friends, here’s how I was able to make things work again.

Postgres and JSON

First of all, we need to connect to our open-webui database (the rest of the article continues from the psql prompt):

linux # psql --user postgres -h localhost openwebui
psql (17.5 (Debian 17.5-1.pgdg120+1))
Type "help" for help.

openwebui=#

Let’s see what database tables open-webui is using:

openwebui=# \dt
               List of relations
 Schema |       Name       | Type  |   Owner   
--------+------------------+-------+-----------
 public | alembic_version  | table | openwebui
 public | auth             | table | openwebui
 public | channel          | table | openwebui
 public | channel_member   | table | openwebui
 public | chat             | table | openwebui
 public | chatidtag        | table | openwebui
 public | config           | table | openwebui
 public | document         | table | openwebui
 public | feedback         | table | openwebui
 public | file             | table | openwebui
 public | folder           | table | openwebui
 public | function         | table | openwebui
 public | group            | table | openwebui
 public | knowledge        | table | openwebui
 public | memory           | table | openwebui
 public | message          | table | openwebui
 public | message_reaction | table | openwebui
 public | migratehistory   | table | openwebui
 public | model            | table | openwebui
 public | note             | table | openwebui
 public | prompt           | table | openwebui
 public | tag              | table | openwebui
 public | tool             | table | openwebui
 public | user             | table | openwebui
(24 rows)

Ok, “config” sounds quite promising.

The blob

So the first approach is to just get everything from that table:

openwebui=# SELECT * FROM config;
id | data | updated_at | version | created_at
1  | <...> | <...>

The first (and most likely only) entry contains one single JSON blob in column data (I didn’t include it for better readability).

Reading JSON data

As you can imagine getting a single value from that unformatted JSON object is a mess. So lets go step by step and have a look at the topmost elements of the JSON object:

openwebui=# SELECT json_object_keys(data) FROM config WHERE id = 1;
 json_object_keys 
------------------
 version
 ui
 openai
 ollama
 image_generation
 webhook_url
 auth
 webui
 channels
 user
 evaluation
 audio
 rag
 google_drive
 direct
 task
 onedrive
(17 rows)

Now let’s try to get a single attribute value (e.g. “version“) using the ->> operator:

openwebui=# SELECT data->>'version' FROM config WHERE id = 1;
 ?column? 
----------
 0
(1 row)

So simple queries seem to work nicely.

However the error I am trying to correct is stored in the sub-section “image_generation” which by itself is a JSON object:

openwebui=# SELECT data->>'image_generation' FROM config WHERE id = 1;
?column?
-----------
{"engine": "comfyui", "enable": true, <...>

Reading that specific value works with the #> operator:

openwebui=# SELECT data#>'{image_generation,enable}' FROM config WHERE id = 1;
 ?column? 
----------
 true
<...>

As my problems started with enabling image_generation, I’d like to reset the corresponding “enable” value to “false“.

Modifying JSON data

…seems to be a different beast however:

I was hoping for a simple 1-line solution, but most results were quite complex SQL statements that seemed to be “over the top” for my intended purpose.

Also many of the suggested solutions used jsonb functions (but the datatype used by open-webui is only json). So after quite some research I found the following 1-liner as the most compact solution (including some json<->jsonb conversion magic):

openwebui=# UPDATE config SET data=jsonb_set(data::jsonb, '{image_generation,enable}', jsonb 'false')::json WHERE id = 1;
UPDATE 1
openwebui_data=# SELECT data#>'{image_generation,enable}' FROM config WHERE id = 1;
 ?column? 
----------
 false
<...>

So basically we convert the ‘data‘ object for jsonb on the fly, modify the desired attributes value with jsonb_* functions and write back the data.

That’s it. Restart open-webui and the admin settings page is accessible again!