[PSQL JSON]Tips to work with JSON/JSONB in PostgreSQL #psql #json

psql-work-with-json.sql

-- last element in a JSON array, https://www.appsloveworld.com/postgresql/100/89/find-last-element-of-array-in-json-column-type
select '[[1479772800000, 70.12], [1479859200000, 70.83], [1480032000000, 71.23]]'::json->-1;

-- check if JSON array contains a string, https://stackoverflow.com/a/27144175
create table rabbits (rabbit_id bigserial primary key, info json not null);
insert into rabbits (info) values
  ('{"name":"Henry", "food":["lettuce","carrots"]}'),
  ('{"name":"Herald","food":["carrots","zucchini"]}'),
  ('{"name":"Helen", "food":["lettuce","cheese"]}');
  
alter table rabbits alter info type jsonb using info::jsonb;
create index on rabbits using gin ((info->'food'));
select info->>'name' from rabbits where info->'food' ? 'carrots';

-- [PostgreSQL Array: The ANY and Contains trick - Postgres OnLine Journal](https://www.postgresonline.com/journal/archives/228-PostgreSQL-Array-The-ANY-and-Contains-trick.html)