CREATE TABLE cards ( id integer NOT NULL, board_id integer NOT NULL, data jsonb );
INSERT INTO cards VALUES (1, 1, '{"name": "Paint house", "tags": ["Improvements", "Office"], "finished": true}');
SELECT data->>'name' AS name FROM cards
SELECT * FROM cards WHERE data->>'finished' = 'true';
CREATE INDEX idxfinished ON cards ((data->>'finished'));
As of Postgres 9.4, along with the JSONB data type came GIN (Generalized Inverted Index) indexes. With GIN indexes, we can quickly query data using the JSON operators @>, ?, ?&, and ?
CREATE INDEX idxgindata ON cards USING gin (data);
SELECT count(*) FROM cards WHERE data @> '{"tags": ["Clean", "Kitchen"]}';
How to query JSONB, beginner sheet cheat – Hacker Noon
PostgreSQL: Documentation: 10: 8.14. JSON Types
How to query JSONB, beginner sheet cheat – Hacker Noon
PostgreSQL: Documentation: 10: 8.14. JSON Types
