Let’s say we have to query a table with a JSONB column on a PostgreSQL 9.5+ database. user metadata 1. Select items by the value of a first level attribute (#1 way) You can query with the operator on . This operator can compare partial JSON strings against a JSONB column. It’s the operator. For this case you may need to add a GIN index on column. @> metadata containment metadata * metadata @> ; SELECT FROM users WHERE '{"country": "Peru"}' 2. Select items by the value of a first level attribute (#2 way) The operator gets a JSON object field as text. Use it if you want to query a simple field in a JSONB column. You might add a B-tree index on . ->> metadata->>'country' * metadata->> = ; SELECT FROM users WHERE 'country' 'Peru' 3. Select item attribute value Once again, the operator gets a JSON object field as text. Just use directly it in the . ->> SELECT metadata->> ; SELECT 'country' FROM users 4. Select only items where a particular attribute is present You can use the operator with the classic operator you use on text: , , , etc. Do not forget to index with a B-tree index. ->> = <> IS NULL metadata->>'country' * metadata->> ; SELECT FROM users WHERE 'country' IS NOT NULL 5. Select items by the value of a nested attribute You can use both or , just like for first level attribute. Add an index according to your choice. @> ->> * metadata-> ->> = ; * metadata @> ; SELECT FROM users WHERE 'company' 'name' "Mozilla" SELECT FROM users WHERE '{"company":{"name": "Mozilla"}}' 6. Select items by the value of an attribute in an array Remembering operator checks containment in a JSONB column, you can query on an array like by just passing to the clause: @> {"x": ["a", "b", "c"]"} {"x":["a"]} WHERE * metadata @> ; SELECT FROM users WHERE '{"companies": ["Mozilla"]}' 7. IN operator on attributes Sometimes, we may need to select items where the attributes inside a JSONB column matches a bunch of possible values. * metadata->> ( , ); SELECT FROM users WHERE 'countries' IN 'Chad' 'Japan' 8. Insert a whole object Use as usual and pass the whole object as JSON. UPDATE ... SET metadata = ; UPDATE users SET '{"country": "India"}' 9. Update or insert an attribute Use the operator to concatenate the actual data with the new data. It will update or insert the value. || metadata = metadata || ; UPDATE users SET '{"country": "Egypt"}' 10. Removing an attribute The operator - removes a key from an object. metadata = metadata - ; UPDATE users SET 'country' Final note Querying on JSONB objects is almost as simple as classic SQL queries. I posted only a few examples here, about what seems the most common use cases to me. It’s a note for me, I hope it could help other people too. You may dig in PostgreSQL docs, which has many more examples and more precise explanations : www.postgresql.org PostgreSQL: Documentation: 9.6: JSON Functions and Operators Note: There are parallel variants of these operators for both the json and jsonb types. The field/element/path… PostgreSQL: www.postgresql.org Documentation: 9.6: JSON Types PostgreSQL allows only one character set encoding per database. It is therefore not possible for the JSON types to… Feel free to comment with advices, feedback and criticism. I would be really happy to learn more. Thanks to (once again) for his review. Emilien Schneider
