json is basically a blob that stores JSON data in raw format, preserving even insignificant things such as whitespace, the order of keys in objects, or even duplicate keys in objects. It does offer the ability to do some basic JSON operations such as extracting the value associated with some key in an object, albeit … Read more
SELECT jsonb_array_length(‘[“question”,”solved”]’) AS length; or SELECT json_array_length(‘[“question”,”solved”]’) AS length;
There is no equality (or inequality) operator for the data type json as a whole, because equality is hard to establish. Consider jsonb in Postgres 9.4 or later, where this is possible. More details in this related answer on dba.SE (last chapter): How to remove known elements from a JSON[] array in PostgreSQL? SELECT DISTINCT … Read more
If the data column is text type, then use ->> on cast: select * from module_data where data::json->>’title’ like ‘%Board%’ If it’s already json: select * from module_data where data->>’title’ like ‘%Board%’
jsonb in Postgres 9.4+ The binary JSON data type jsonb largely improves index options. You can now have a GIN index on a jsonb array directly: CREATE TABLE tracks (id serial, artists jsonb); — ! CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists); No need for a function to convert the array. This would support … Read more
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