UPDATED CREATE OR REPLACE FUNCTION jsonb_diff_val(val1 JSONB,val2 JSONB) RETURNS JSONB AS $$ DECLARE result JSONB; v RECORD; BEGIN result = val1; FOR v IN SELECT * FROM jsonb_each(val2) LOOP IF result @> jsonb_build_object(v.key,v.value) THEN result = result – v.key; ELSIF result ? v.key THEN CONTINUE; ELSE result = result || jsonb_build_object(v.key,’null’); END IF; END LOOP; … Read more
Thanks Vlad Mihalcea we have such opportunity! ) He created hibernate-types lib: <dependency> <groupId>com.vladmihalcea</groupId> <artifactId>hibernate-types-52</artifactId> <version>2.1.1</version> </dependency> which adds a support of ‘json’, ‘jsonb’ and other types to Hibernate: @Data @NoArgsConstructor @Entity @Table(name = “parents”) @TypeDefs({ @TypeDef(name = “string-array”, typeClass = StringArrayType.class), @TypeDef(name = “int-array”, typeClass = IntArrayType.class), @TypeDef(name = “json”, typeClass = JsonStringType.class), @TypeDef(name … Read more
Two way, you can test any json(b) value the ->> operator extract value as text. But this operation slow, if you will use the value only test the @> operator test any json(b) contain any json(b). This is a quick but you are not tested NOT option. Simply and quick way: NOT (attribute @> ‘{“City”:”Mesa”}’::jsonb) … 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
The convention implied from the Django code seems to be to store null JSON values as NULL as opposed to as an empty string (as is the convention for the CharField). I say this because of the following: The empty_strings_allowed is inherited from Field in CharField, and is set to True: django/db/models/fields/__init__.py#L96 class Field(RegisterLookupMixin): “””Base … Read more
After looking around I discovered that my postgresql version is not 9.4 by running the right command postgres=# SHOW SERVER_VERSION; server_version —————- 9.1 So I had simply to upgrade my postgresql to 9.4. By the way I followed this article to do the upgrading which I found very handy. Now : postgres=# SHOW SERVER_VERSION; server_version … Read more
Tried this but understood nothing! To fully work with jsonb in Spring Data JPA (Hibernate) project with Vlad Mihalcea’s hibernate-types lib you should just do the following: 1) Add this lib to your project: <dependency> <groupId>com.vladmihalcea</groupId> <artifactId>hibernate-types-52</artifactId> <version>2.2.2</version> </dependency> 2) Then use its types in your entities, for example: @Data @NoArgsConstructor @Entity @Table(name = “parents”) … Read more
SELECT q.id, d.key, d.value FROM q JOIN json_each_text(q.data) d ON true ORDER BY 1, 2; The function json_each_text() is a set returning function so you should use it as a row source. The output of the function is here joined laterally to the table q, meaning that for each row in the table, each (key, … Read more
Simply cast a text to boolean: create table jsonb_test (id int, data jsonb); insert into jsonb_test values (1, ‘{“is_boolean” : true}’), (2, ‘{“is_boolean” : false}’); select id, data, (data->>’is_boolean’)::boolean as is_boolean from jsonb_test where (data->>’is_boolean’)::boolean id | data | is_boolean —-+————————+———— 1 | {“is_boolean”: true} | t (1 row) Note that you can also cast … Read more
ALTER TABLE t ALTER COLUMN j TYPE jsonb USING j::text::jsonb;