Skip to content

Instantly share code, notes, and snippets.

@monsha
Last active May 27, 2020 22:36
Show Gist options
  • Save monsha/fbb9c28b0494fbc107637b84df086caf to your computer and use it in GitHub Desktop.
Save monsha/fbb9c28b0494fbc107637b84df086caf to your computer and use it in GitHub Desktop.
PostgreSQL JSON cheatsheet
-- 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'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment