Search code examples
sqljsonpostgresqlsql-updatejsonb

Remove a set of strings from a JSONB array of strings

In PostgreSQL, I have a table named users with data that looks like so:

id favorites
1 { "shapes": ["Triangle", "Square", "Rectangle", "Circle"] }
2 { "shapes": ["Rectangle", "Triangle", "Circle"] }
... ...

Where favorites is a JSONB column.

I want to remove shapes "Square" and "Circle" to all users' favorite shapes. This is a list of shapes to remove which can be of variable length. The output table should look like so:

id favorites
1 { "shapes": ["Triangle", "Rectangle"] }
2 { "shapes": ["Rectangle", "Triangle"] }
... ...

How do I do that in one query?

Limitations:

  • The array of shapes to remove may contain elements that do not exist in certain rows. In this case, the shapes that are not in the row should be ignored, while the shapes that do exist should be removed.
  • The favorites object contains other fields than shapes and therefore cannot be converted into an array of strings.
  • The update must be in one query
Solution

  • You can delete a whole array of string values at once. Postgres has a separate operator for that. The manual:

    jsonb - text[] → jsonb

    Deletes all matching keys or array elements from the left operand.

    UPDATE tbl
SET    favorites = jsonb_set(favorites, '{shapes}', (favorites -> 'shapes') - '{Square, Circle}'::text[])
WHERE  favorites IS DISTINCT FROM jsonb_set(favorites, '{shapes}', (favorites -> 'shapes') - '{Square, Circle}'::text[]);

    fiddle

    Make sure the array has the proper type ('{Square, Circle}'::text[]), else the operator can be ambiguous, and Postgres complains. (Typically, you pass a typed array value instead of the array literal in my example and all is fine.)

    The added WHERE clause skips all the rows that would not change anyway. Else, your query does (a lot of) useless work. See: