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
As a workaround to avoid the ? operator, you could create a new operator doing exactly the same. This is the code of the original operator: CREATE OPERATOR ?( PROCEDURE = jsonb_exists, LEFTARG = jsonb, RIGHTARG = text, RESTRICT = contsel, JOIN = contjoinsel); SELECT ‘{“a”:1, “b”:2}’::jsonb ? ‘b’; — true Use a different name, … Read more
You can use direct call to a function jsonb_exists_any(). So in your case it would be jsonb_exists_any(u.authorities::jsonb, array[‘ROLE_1’, ‘ROLE_2’])
In short – use JSONB instead of JSON or cast JSON to JSONB. You cannot compare json values. You can compare text values instead: SELECT * FROM movie_test WHERE tags::text=”[“dramatic”,”women”,”political”]” Note however that values of type JSON are stored as text in a format in which they are given. Thus the result of comparison depends … Read more
Use a jsonb (not jsonb[]!) column with the structure like this: select ‘[{ “adresse_line-1”: “a11”, “adresse_line-2”: “a12”, “postalcode”: “code1” }, { “adresse_line-1”: “a21”, “adresse_line-2”: “a22”, “postalcode”: “code2” } ]’::jsonb; Though, a regular table related to the main one is a better option. Why not jsonb[]? Take a look at JSON definition: JSON is built on … Read more
You should merge unnested elements using jsonb_each() for both values. Doing this in a non-trivial query may be uncomfortable, so I would prefer a custom function like this one: create or replace function jsonb_my_merge(a jsonb, b jsonb) returns jsonb language sql as $$ select jsonb_object_agg( coalesce(ka, kb), case when va isnull then vb when vb … 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
There is no single operation, which can help you, but you have a few options: 1. If you have a small (and fixed) number of ids to query, you can use multiple containment operators @> combined with or; f.ex.: where data @> ‘[{“id”: “1884595530”}]’ or data @> ‘[{“id”: “791712670”}]’ A simple gin index can help … Read more
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
You can find an index of a searched element using jsonb_array_elements() with ordinality (note, ordinality starts from 1 while the first index of json array is 0): select pos- 1 as elem_index from samples, jsonb_array_elements(sample->’result’) with ordinality arr(elem, pos) where id = 26 and elem->>’8410′ = ‘FERR_R’; elem_index ———— 2 (1 row) Use the above … Read more