May 9, 2017

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: