PostgreSQL

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.