Skip to Content

Postgres jsonb: SQL queries on JSON fields

1. Select rows that have specific JSON field set in jsonb column

FROM table
WHERE (col->>'field') IS NOT NULL;

2. Update a specific JSON field in jsonb column, if not set

UPDATE table
SET col = jsonb_set(col, '{"field"}', '"value"')
WHERE (col->>'field') IS NULL;

3. Remove a specific JSON field from jsonb column

UPDATE table
SET col = col #- '{"field"}';

Full documentation:


Join in on the conversation with Vojtech Vitek when you subscribe to Vojtech's IT Blog.