How to Query a JSON Column in PostgreSQL
One of PostgreSQL's benefits is that it's a relational database, but you can also get the advantages of unstructured data by storing things in a JSON column. Here's how you can query your JSON column in PostgreSQL:
-- Give me params.name (text) from the events table
select params->>'name' from events;
-- Find only events with a specific name
select * from events where params->>'name' = 'Click Button';
-- Give me the first index of a JSON array
select params->ids->0 from events;
-- Find users where preferences.beta is true (boolean)
-- This requires type casting preferences->'beta' from json to boolean
select preferences->'beta' from users where (preferences->>'beta')::boolean is true;The short arrow -> keeps the type as JSON, and the long arrow ->> returns text.
Shared queries and folders ✅ Version history ✅ One-click connection to PostgreSQL ✅
Get more done, together, with PopSQL and PostgreSQL