PostgreSQL JSON cheatsheet

json.sql

-- return details.approved content as text 
select details->>'approved' from events

-- value (json)
select details -> 'title' -> 'value' as value as from events

-- with a specific value
select * from events where details->>'title' = 'some'
select * from events where where details -> 'title' ->> 'value' = 'some'
select * from events where (details-> 'approved' ->> 'value')::boolean is true
select * from events where details -> 'approved' @> '{"value": true}'

--  key exist at the top level?
select * from events where details ? 'approved' is false

-- any of these keys exist at the top level?
select * from events where details ?| array['approved','deleted'] is true

-- all of these keys exist at the top level?
select * from events where details ?& array['approved','deleted'] is true

-- expand a json array to a set of rows
select id, jsonb_array_elements_text(tmp.values) as value
from (select t.id, t.details -> 'values'::text as values from events t) tmp

-- returns all json keys to a set of rows (jsonb)
select t.details -> jsonb_object_keys(t.details) as keys from events t

-- using similar
SELECT * FROM table
WHERE json_column::text similar to '%("key": "value")%'


—— select count(*) alternative
SELECT reltuples::bigint
FROM pg_catalog.pg_class
WHERE relname = 'mytable'