We know, Psql had JSON (JavaScript Object Notation) support for a while, but it wasn’t that great due to lack of indexing and key extractor methods. JSON has the benefit of compatibility with many languages (especially JavaScript), making it perfect for inter-application communication. JSON most widely used for communicating between the web server and client. With the release of v9.4, JSON support tried to make the question “Do I use a document database or relational DB?” unnecessary. Why not have both?.
Postgres v9.4 added the ability to store JSON as “Binary JSON” or “JSONB”. Both accept almost identical sets of Input values. The major practical difference is the efficiency factor. The JSONB is stored in a decomposed binary format making it slightly slower to input on account of added conversion overhead and JSON stores an exact copy of input text, but significantly faster in the process, as no reparsing is needed.
Advantages of JSON in Postgres:
* You can dump data into the database however it comes, without having to adapt it to any specialized database language.
* Better to split out aspects of complex ACL (Access Control Lists) into a NoSQL environment to ensure speedy responses on queries
* The efficient way to manage geographical data.
* An easy way to pass complex results easily back to the application
* Help to develop multi-tenant applications easily.
* Store relatively free-form data for later processing
* Create custom fields and flexibility in a better way (No need to use customField2, customField3, etc..)
* Cases dealing with people's names. ( Example: people"s names can contain numbers, have an indefinite number of names, not have a first name, a middle name or a last name, have more than one canonical full name, people's names are not necessarily written in ASCII, and not necessarily written in a single character set, people might not have names)
A possible way of doing it is by using a JSON:
{
"first_name" : "Ronaldo",
"mother_name": "de Assis",
"last_name" : "Moreira",
"nicknames" :["Ronaldinho",
"Gaúcho"]
}JSON or JSONB? Which to Use?
- In general, unless you are just storing the data, use JSONB for stored data
- For unstored data, JSON can sometimes be better
- JSON column will store the JSON string "as is" as long as it's valid. This could mean that the data has more than one items with the same key. JSONB will parse it for indexing sake, trimming extra whitespace, etc so if you had 2 values with the same key only one would be left
- Benefits of JSONB
1. more efficiency
2. significantly faster to process
3. supports indexing (which can be a significant advantage)
4. Simpler schema designs (replacing entity-attribute-value (EAV) tables JSONB columns, which can be queried, indexed and joined, allowing for performance improvements up until 1000X!)
- Drawbacks of JSONB
1. slightly slower input (due to added conversion overhead),
2. it may take more disk space than plain JSON due to a larger table footprint, though not always,
3. Certain queries (especially aggregate ones) may be slower due to the lack of statistics.
| JSON primitive type | PostgreSQL type | Notes |
| string | text | \u0000 is disallowed, as are non-ASCII Unicode escapes if database encoding is not UTF8 |
| number | numeric | NaN and infinity values are disallowed |
| boolean | boolean | Only lowercase true and false spellings are accepted |
| null | (none) | SQL NULL is a different concept |
Basic Query operations
1. Table Creation
CREATE TABLE class_room (
id integer NOT NULL,
board_id integer NOT NULL,
data jsonb
);
INSERT INTO class_room VALUES (1, '{"name": "SIX B", "teachers": ["Ashley John", "Antoine Langlais"], "exam_conducted": false}');
INSERT INTO class_room VALUES (2, '{"name": "SEVEN A", "teachers": ["Ashley Presley", "David Samson", "Famke Jenssens"], "exam_conducted": false}');
INSERT INTO class_room VALUES (3, '{"name": "SIX A", "teachers": ["Hans Anders", "Jack Macklin", "Jan Van Eyck"], "failed_students": ["John Doe", "John Smith"], "exam_conducted": true}');
INSERT INTO class_room VALUES (4, '{"name": "FIVE D", "teachers": ["Jean-Pierre Carnaud", "Jimmy Kosikin"], "exam_conducted": false}');
INSERT INTO class_room VALUES (5, '{"name": "FOUR A", "teachers": ["João Gomer", "David Samson"], "exam_conducted": false}');
3. Query Data
SELECT data->'teachers' AS teachers FROM class_room;
Result->
| teachers jasonb |
| 1 | "["Ashley John", "Antoine Langlais"]" |
| 2 | "["Ashley Presley", "David Samson", "Famke Jenssens"]" |
| 3 | "["Hans Anders", "Jack Macklin", "Jan Van Eyck"]" |
| 4 | "["Jean-Pierre Carnaud", "Jimmy Kosikin"]" |
| 5 | "["João Gomer", "David Samson"]" |
In the above, we just created a table with JSON data type. How we use JSON in a table without a JSON data type? We can create JSON values from any table by some native JSON functions.
Some of the most helpful functions are:
1. to_json(anyelement), to_jsonb(anyelement) : Returns the value as JSON or JSONB. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to JSON, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid JSON or JSONB value.
Example:
SELECT to_json (r) FROM (SELECT e.name AS employee, d.name AS department
FROM hr_employee e INNER JOIN hr_department d ON
d.id = e.department_id) r;
Result->
| to_json jason |
| 1 | "{"employee":"Michael Hawkins","department":"Research & Development"}" |
| 2 | "{"employee":"Liam Nelson","department":"Research & Development"}" |
| 3 | "{"employee":"David Samson","department":"Research & Development"}" |
| 4 | "{"employee":"Jack Macklin","department":"Research & Development"}" |
| 5 | "{"employee":"Martin Lawrence","department":"Professional Services"}" |
2. json_each_text(json), jsonb_each_text(jsonb) : Expands the outermost JSON object into a set of key/value pairs. The returned values will be of type text.
Example:
select * from json_each('{"a":"foo", "b":"bar"}')
Result->
| key text | value json |
| 1 | a | “foo” |
| 2 | b | “bar” |
3. json_build_array(anyelement): Builds a possibly-heterogeneously-typed JSON array out of a variadic argument list
Example:
SELECT json_build_array (r) FROM (SELECT name from hr_employee WHERE id<5) r;
Result->
| json_build_array json |
| 1 | "[{"name":"Michael Hawkins"}]" |
| 2 | "[{"name":"John Smith"}]" |
| 3 | "[{"name":"Pieter Parker"}]" |
| 4 | "[{"name":"Antoine Langlais"}]" |
4. array_agg(expression): input values, including nulls, concatenated into an array
Example:
SELECT array_agg (r) FROM (SELECT name from hr_employee WHERE id<5) r;
Result->
| array_agg record[] |
| 1 | "{"(\"Michael Hawkins\")","(\"John Smith\")","(\"Pieter Parker\")","(\"Antoine Langlais\")"}" |
5. json_agg(expression): aggregates values as a JSON array
Example:
SELECT json_agg (r) FROM (SELECT name from hr_employee WHERE id<5) r;
Result->
| json_agg json |
| 1 | "[{"name":"Michael Hawkins"}, {"name":"John Smith"}, {"name":"Pieter Parker"}, {"name":"Antoine Langlais"}]" |
If you’re already a MongoDB or Postgres user, changing track might feel like a massive pain in the neck, but believe us, you’ll want to get this right, as soon as you can. As your data keeps growing and getting more complex, turning that ship around will only get tougher!