Sunday, February 24, 2019

Posgres as "Document DB" with JSONB

Postgres JSON: Unleash the Power of Storing JSON in Postgres | Codeship | via @codeship

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

No comments: