Created
May 27, 2019 08:48
-
-
Save jonathanbp/b38b62d26dd1c2f63dba420d4cd76d38 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Create a table with a JSONB column. | |
| CREATE TABLE items ( | |
| id SERIAL PRIMARY KEY, | |
| attrs JSONB | |
| ); | |
| -- You can insert any well-formed json input into the column. Note that only | |
| -- lowercase `true` and `false` spellings are accepted. | |
| INSERT INTO items (attrs) VALUES ('{ | |
| "name": "Pasta", | |
| "ingredients": ["Flour", "Eggs", "Salt", "Water"], | |
| "organic": true, | |
| "dimensions": { | |
| "weight": 500.00 | |
| } | |
| }'); | |
| -- Create an index on all key/value pairs in the JSONB column. | |
| CREATE INDEX idx_items_attrs ON items USING gin (attrs); | |
| -- Create an index on a specific key/value pair in the JSONB column. | |
| CREATE INDEX idx_items_attrs_organic ON items USING gin ((attrs->'organic')); | |
| -- The -> operator is used to get the value for a key. The returned value has | |
| -- the type JSONB. | |
| SELECT attrs->'dimensions' FROM items; | |
| SELECT attrs->'dimensions'->'weight' FROM items; | |
| -- Or you can use ->> to do the same thing, but this returns a TEXT value | |
| -- instead. | |
| SELECT attrs->>'dimensions' FROM items; | |
| -- You can use the returned values as normal, although you may need to type | |
| -- cast them first. | |
| SELECT * FROM items WHERE attrs->>'name' ILIKE 'p%'; | |
| SELECT * FROM items WHERE (attrs->'dimensions'->>'weight')::numeric < 100.00; | |
| -- Use ? to check for the existence of a specific key. | |
| SELECT * FROM items WHERE attrs ? 'ingredients'; | |
| -- The ? operator only works at the top level. If you want to check for the | |
| -- existence of a nested key you can do this: | |
| SELECT * FROM items WHERE attrs->'dimensions' ? 'weight'; | |
| -- The ? operator can also be used to check for the existence of a specific | |
| -- text value in json arrays. | |
| SELECT * FROM items WHERE attrs->'ingredients' ? 'Salt'; | |
| -- Use @> to check if the JSONB column contains some specific json. This can | |
| -- be useful to filter for a specific key/value pair like so: | |
| SELECT * FROM items WHERE attrs @> '{"organic": true}'::jsonb; | |
| SELECT * FROM items WHERE attrs @> '{"dimensions": {"weight": 10}}'::jsonb; | |
| -- Note that @> looks for *containment*, not for an exact match. The | |
| -- followingquery will return records which have both "Flour" and "Water" | |
| -- as ingredients, rather than *only* "Flour" and "Water" as the ingredients. | |
| SELECT * FROM items WHERE attrs @> '{"ingredients": ["Flour", "Water"]}'::jsonb; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment