postgresql migrating JSON to JSONB [duplicate]
ALTER TABLE table_with_json ALTER COLUMN my_json SET DATA TYPE jsonb USING my_json::jsonb;
jsonb
PostgreSQL rename attribute in jsonb field
In UPDATE use delete (-) and concatenate (||) operators, e.g.: create table example(id int primary key, js jsonb); insert into example values (1, ‘{“nme”: “test”}’), (2, ‘{“nme”: “second test”}’); update example set js = js – ‘nme’ || jsonb_build_object(‘name’, js->’nme’) where js ? ‘nme’ returning *; id | js —-+————————- 1 | {“name”: “test”} 2 … Read more
How to convert PostgreSQL 9.4’s jsonb type to float
There are two operations to get value from JSON. The first one -> will return JSON. The second one ->> will return text. Details: JSON Functions and Operators Try SELECT (json_data->’position’->>’lat’)::float + 1.0 AS lat FROM updates LIMIT 5
postgres jsonb_set multiple keys update
The documentation says: The || operator concatenates the elements at the top level of each of its operands. … For example, if both operands are objects with a common key field name, the value of the field in the result will just be the value from the right hand operand. So using your example data: … Read more
Difference between JSON and JSONB in Postgres [duplicate]
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
Appending (pushing) and removing from a JSON array in PostgreSQL 9.5+
To add the value use the JSON array append opperator (||) UPDATE jsontesting SET jsondata = jsondata || ‘[“newString”]’::jsonb WHERE id = 7; Removing the value looks like this UPDATE jsontesting SET jsondata = jsondata – ‘newString’ WHERE id = 7; Concatenating to a nested field looks like this UPDATE jsontesting SET jsondata = jsonb_set( … Read more
Calculate JSONB Array Length Using PostgreSQL 9.4
SELECT jsonb_array_length(‘[“question”,”solved”]’) AS length; or SELECT json_array_length(‘[“question”,”solved”]’) AS length;
How to query a json column for empty objects?
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
Postgresql json like query
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%’
Index for finding an element in a JSON array
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