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.