jsonb in Postgres 9.4+ You can use the same query as below, just with jsonb_array_elements(). But rather use the jsonb “contains” operator @> in combination with a matching GIN index on the expression data->’objects’: CREATE INDEX reports_data_gin_idx ON reports USING gin ((data->’objects’) jsonb_path_ops); SELECT * FROM reports WHERE data->’objects’ @> ‘[{“src”:”foo.png”}]’; Since the key objects … Read more
If you’re able to upgrade to Postgresql 9.5, the jsonb_set command is available, as others have mentioned. In each of the following SQL statements, I’ve omitted the where clause for brevity; obviously, you’d want to add that back. Update name: UPDATE test SET data = jsonb_set(data, ‘{name}’, ‘”my-other-name”‘); Replace the tags (as oppose to adding … Read more
First, hstore is a contrib module, which only allows you to store key => value pairs, where keys and values can only be texts (however values can be sql NULLs too). Both json & jsonb allows you to store a valid JSON value (defined in its spec). F.ex. these are valid JSON representations: null, true, … Read more