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
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
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
First of all, you cannot access JSON array values like that. For a given json value: [{“event_slug”:”test_1″,”start_time”:”2014-10-08″,”end_time”:”2014-10-12″}, {“event_slug”:”test_2″,”start_time”:”2013-06-24″,”end_time”:”2013-07-02″}, {“event_slug”:”test_3″,”start_time”:”2014-03-26″,”end_time”:”2014-03-30″}] A valid test against the first array element would be: WHERE e->0->>’event_slug’ = ‘test_1′ But you probably don’t want to limit your search to the first element of the array. With the jsonb data type in … Read more
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
This particular case The function below dynamically creates a view based on a table: create or replace function create_totals_view(table_name text) returns void language plpgsql as $$ declare s text; begin execute format ($fmt$ select string_agg(format(‘star_pu->>”%s” “%s”‘, key, key), ‘,’) from ( select distinct key from %s, json_each(star_pu) order by 1 ) s; $fmt$, ‘%s’, ‘%s’, … 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
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
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
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