JSON (Javascript Object Notation) is supported in PostgreSQL which lets you store semi-structured or unstructured data in a table and allows for greater flexibility for applications and support for NoSQL like features. Currently, there are two JSON data types in PostgreSQL: JSON and JSONB. In this post, I want to try some common operations for JSON/JSONB for future reference. The examples were tested on PostgreSQL 9.5.2.
What is the difference between JSON and JSONB data type?
In PostgreSQL, JSONB type has some advantages over the JSON:
- JSONB is more efficient: it stores data in binary format, which it has to convert from the input text during the initial insertion, but afterwards it no longer requires to re-parse the text
- JSONB has more functions than JSON
- JSONB supports indexing
- JSONB removes white spaces from the input text
- JSONB removes duplicates by selecting the last value of a duplicate key
Since JSONB has more features than JSON, I will mainly look at the JSONB functions. In many cases (but not all), JSON has similar functionality. Please take a look of full list of JSON & JSONB functions in the official documentation.
What is the difference between a JSONB object and JSONB array?
JSONB object begins with curly braces that contains key:value pairs. For example: ‘{“id”: “123”, “name”: “Roger”}’::jsonb.
JSON Array starts with array brackets and contains a list of items. For example: ‘[“id”, “name”]’::jsonb. Depending on the application, we may prefer one type over another.
How to select values from a JSONB object or array?
There are several operators and functions that can be used to extract value from a JSON/JSONB object/array. We can use ‘->’ operator to find the value of a specific key in JSON/JSONB object/array or use ‘->>’ to find the same value in text format. Similarly, we can use ‘#>’ operator to find the value at specific path in JSON/JSONB object/array or use ‘#>>’ to find the same value in text format. Let’s look at an example for JSONB object:
with obj as (
select '{"id": "123",
"name": "Roger",
"tags": ["sports", "not-for-profit"],
"address": {
"street_number": "123",
"street_name": "abc street",
"city": "disneyland"}}'::jsonb as jsonb_object
)
select
jsonb_object -> 'id' as "id as JSON",
jsonb_object ->> 'id' as "id as text",
jsonb_object #> '{address, city}' as "city as JSON",
jsonb_object #>> '{address, city}' as "city as text"
from
obj;
id as JSON | id as text | city as JSON | city as text
------------+------------+--------------+--------------
"123" | 123 | "disneyland" | disneyland
(1 row)As we can see, we are able to extract the value of a particular key using the ‘->’ or ‘->>’ operators, or value at certain path using ‘#>’ or ‘#>>’ operators.
Now let’s look at a JSONB array. The operators work similarly to JSONB objects, you just have to specify the position instead of the key.
with arr as (
select '[{"id": "123"},
{"name": "Roger"},
{"tags": ["sports", "not-for-profit"]},
{"address": {
"street_number": "123",
"street_name": "abc street",
"city": "disneyland"}}]'::jsonb as jsonb_array
)
select
jsonb_array -> 0 as "1st element as JSON",
jsonb_array ->> 0 as "1st element as text",
jsonb_array -> 0 -> 'id' as "access internal element (id)"
from
arr;
1st element as JSON | 1st element as text | access internal element (id)
---------------------+---------------------+------------------------------
{"id": "123"} | {"id": "123"} | "123"
(1 row)There are also two functions which does the same thing, json_extract_path() & json_extract_path_text(). As the names suggest, the difference between them is that one returns the result as JSON, whereas as the other returns the result as text.
Here are some examples:
select json_extract_path('{"id": "123", "name": {"first": "Roger", "last": "federer"}}', 'name');
--> {"first": "Roger", "last": "federer"}
select json_extract_path_text('{"id": "123", "name": {"first": "Roger", "last": "federer"}}', 'name');
--> {"first": "Roger", "last": "federer"}How to find if one JSONB object contains another?
There are a few operators that can be used to compare two JSONB objects or arrays. We can use ‘@>’ which returns true if the left side object contains the right side object. We can use ‘<@’, which returns true if the right side object contains the left side object.
A few examples hopefully will make it a bit clear.
select '{"id": "123", "name": "Roger"}'::jsonb @> '{"name": "Roger"}'::jsonb;
--> returns true, as the left side contains the right side
select '{"name": "Roger"}'::jsonb <@ '{"id": "123", "name": "Roger"}'::jsonb;
--> returns true, as the right side contains the left sideHow to find if a particular key is present in a JSON object?
We can use ‘?’ operator, which returns true if the key is present at the top level in a JSONB object. There is also ‘?|’, which returns true if any of the keys on the right side is present in the left side object, and ‘?&’, which returns true if all of the right side keys are present in the left side object.
select '{"id": "123", "name": "Roger"}'::jsonb ? 'id';
--> returns true, as the right side string exist as key in the left side object
select '{"id": "123", "name": "Roger"}'::jsonb ? 'city';
--> returns false, as the right side string does not exist as key in the left side object
select '{"id": "123", "name": "Roger"}'::jsonb ?| array['id', 'city'];
--> returns true, as one of the right side strings is present as key in the left side object
select '{"id": "123", "name": "Roger"}'::jsonb ?& array['id', 'city'];
--> returns false, as not both right side string are present in the left side object How to find all keys in a JSONB object?
We can use the function json_object_keys() to find all the top level keys in a JSONB object.
select json_object_keys('{"id": "123", "name": {"first": "Roger", "last": "federer"}}');k
json_object_keys
------------------
id
name
(2 rows)How to concatenate two JSONB objects or arrays?
We can use ‘||’ operator to concatenate two JSONB objects or arrays.
For example:
select '{"id": "123"}'::jsonb || '{"name": "Roger"}'::jsonb;
--> {"id": "123", "name": "Roger"}
--> output is the concatenation of the two objects.
select '[{"id": "123"}, {"name": "Roger"}]'::jsonb || '[{"sport": "tennis"}]'::jsonb;
--> [{"id": "123"}, {"name": "Roger"}, {"sport": "tennis"}]How to delete an element from a JSONB object or array?
We can use ‘-‘ operator to delete an element from a JSONB object or array. For JSONB object, we need to specify the ‘key’ for the key:value pair that we want to delete. For JSONB array, we need to specifiy the position of the element.
Let us take a look at a couple of examples:
select '{"id": "123", "name": "Roger"}'::jsonb - 'name'
--> {"id": "123"}, removed the 'name' key:value pair
select '["id", "name"]'::jsonb - 1
--> ["id"], removed the second element (position starts from 0)We can also use ‘#-‘ operator to delete an element at certain path, which works for both JSONB object and array.
select '{"id": "123", "name": {"first": "Roger", "last": "federer"}}'::jsonb #- '{name, last}';
--> {"id": "123", "name": {"first": "Roger"}}
--> removed key/value pair from the specified path, example for a JSONB object
select '[{"id": "123"}, {"name": "Roger"}]'::jsonb #- '{0, id}' --> [{}, {"name": "Roger"}];
--> removed element from the specified path (position)
--> example for a JSONB array, please note that the position starts from 0
select '[{"id": "123"}, {"name": "Roger"}]'::jsonb #- '{1, name}';
--> [{"id": "123"}, {}]
--> another example of deleting from a JSONB array.
select '[{"name": [{"first": "Roger"}, {"last": "federer"}]}, "id"]'::jsonb #- '{0, name}';
--> [{}, "id"]
--> yet another example for deleting from a JSONB arrayHow to delete any key:value pair from a JSONB object where the value is NULL?
We can use the function jsonb_strip_nulls to delete any entry where the value is null.
select jsonb_strip_nulls('[{"id": null}, {"name": "Roger"}]');
jsonb_strip_nulls
-------------------------
[{}, {"name": "Roger"}]
(1 row)How to update a key:value pair in JSONB?
We can use jsonb_set() function to update the value of a key:value pair in a JSONB object.
For example to update the name of the following JSONB object, we can use:
select jsonb_set('{"id": "123", "name": {"first": "Roger", "last": "federer"}}', '{name, last}', '"Nadal"');
--> {"id": "123", "name": {"last": "Nadal", "first": "Roger"}}
--> Please observe that the 'last' key:value pair appeared before 'first', as unlike array JSON/JSONB objects do not care about relative position. Similarly, we can update a JSONB array as below by specifying the position:
select jsonb_set('[{"id":123, "name":"Roger"}]', '{0,name}', '"Rafael"');
--> [{"id": 123, "name": "Rafael"}]
--> it updated the value of the key 'name'
select jsonb_set('[{"id":123}, {"name":"Roger"}]', '{1,name}', '"Rafael"');
--> [{"id": 123}, {"name": "Rafael"}]
--> it updated the value of the key 'name'
select jsonb_set('[{"id":123}, {"name":"Roger"}]', '{0,name}', '"Rafael"');
--> [{"id": 123, "name": "Rafael"}, {"name": "Roger"}]Something to observe in the example above, because there was no key called ‘name’ in the first element, it created a new one.
If you don’t want to create a new entry if it doesn’t exist while updating, use the flag ‘false’ as below:
select jsonb_set('[{"id":123}, {"name":"Roger"}]', '{0,name}', '"Rafael"', false);
--> [{"id": 123}, {"name": "Roger"}]
--> no change, as there is no key called 'name' in the first element. How to find length of a JSONB array?
We can use jsonb_array_length() function to find the size of a JSONB array. There is no similar function for JSONB object.
select * from jsonb_array_length('[{"id": "123"}, {"name": "Roger"}]');
--> 2How to find the element type in a JSONB object
We can find the type of the top elements in a JSONB object or array using the function jsonb_typeof(). For example:
select jsonb_typeof('{"id": "123", "name": {"first": "Roger", "last": "federer"}}');
--> object
select jsonb_typeof('[{"id": "123"}, {"name": "Roger"}]');
--> array
select json_typeof('[1, 2, 3, 4]');
--> array
select jsonb_typeof('123');
--> numberHow to pretty print a JSONB object with proper indentation?
We can use jsonb_pretty() function to indent the output format.
select jsonb_pretty('{"id": "123", "name": {"first": "Roger", "last": "federer"}}');
jsonb_pretty
----------------------------
{ +
"id": "123", +
"name": { +
"last": "federer",+
"first": "Roger" +
} +
}
(1 row)How to expand data from JSONB object or array as a table?
We can use json_each() function to extract the key: value as a table in (text, json) format or json_each_text() to expand the same in (text, text) format from a JSONB object.
select * from jsonb_each('{"id":"123", "name":"Roger"}');
key | value
------+---------
id | "123"
name | "Roger"
(2 rows)
select * from jsonb_each_text('{"id":"123", "name":"Roger"}');
key | value
------+-------
id | 123
name | Roger
(2 rows)Similarly, we can use jsonb_array_elements() and jsonb_array_elements_text() to extract elements of a JSONB array as each row.
select * from jsonb_array_elements('[{"id": "123"}, {"name": "Roger"}]');
value
-------------------
{"id": "123"}
{"name": "Roger"}
(2 rows)
select * from jsonb_array_elements_text('[{"id": "123"}, {"name": "Roger"}]');
value
-------------------
{"id": "123"}
{"name": "Roger"}
(2 rows)How to create a record from a JSONB array or object?
We can create a record from a JSONB object using the function jsonb_to_record(). Something to remember we need to explicitly define the type of record.
select * from jsonb_to_record('{"id": "123", "name": {"first": "Roger", "last": "federer"}}') as temp(id text, name text);
id | name
-----+---------------------------------------
123 | {"last": "federer", "first": "Roger"}
(1 row)Similarly, we can use the function jsonb_to_recordset() to convert a JSONB array to record.
select * from jsonb_to_recordset('[{"id": "123"}, {"name": "Roger"}]') as temp(id text, name text);
id | name
-----+-------
123 |
| Roger
(2 rows)How to create JSONB object or array from other data types?
We can use to_json() or to_jsonb() function to convert a text, int or JSON into a JSONB.
select to_jsonb('test');
--> ERROR: could not determine polymorphic type because input has type "unknown"
select to_jsonb('test'::text);
--> "test"
select to_jsonb(1);
--> 1
select to_jsonb('test string "1'::text);
--> "test string \"1"
select to_jsonb(to_json('test'::text));
--> "test"
--> can convert from JSON to JSONBWe can use array_to_json() to convert an array to a JSON array. You can specify the type to determine how the key value will be cast into. Please note there is no array_to_jsonb() function.
select array_to_json('{{1,2},{3,4}}'::int[]);
--> [[1,2],[3,4]]
select array_to_json('{{1,2},{3,4}}'::text[]);
--> [["1","2"],["3","4"]]We can use row_to_json() to return an input row as a JSON object. It adds keys as f1, f2 etc. For example:
select row_to_json(row('a','b'));
--> {"f1":"a","f2":"b"} There are a couple of variadic functions, jsonb_build_array() & jsonb_build_object(), which can take variable number of arguments. The arguments can be of heterogenous types and they are interpreted as key1, value1, key2, value2 etc.The input for jsonb_build_object() must have even number of arguments (but jsonb_build_array() can have odd number of arguments) for obvious reasons.
select jsonb_build_array(1, 2, 3, 'a', 'b', 'c');
--> [1, 2, 3, "a", "b", "c"]
select jsonb_build_array(1, 2, 3, 'a', 'b');
--> [1, 2, 3, "a", "b"]
select jsonb_build_object(1, 2, 3, 'a', 'b', 'c');
--> {"1" : 2, "3" : "a", "b" : "c"}
select jsonb_build_object(1, 2, 3, 'a', 'b');
--> ERROR: argument list must have even number of elements
--> jsonb_build_object() needs to have an even number of arguments, as the arguments are mapped as key -> value. There is another function called jsonb_object() which builds JSON/JSONB objects from a one dimensional even numbered input text array. It is similar to jsonb_build_object() except, in jsonb_build_object(), there can be variable number of inputs, whereas in jsonb_object() the input is a text which contains even number of elements. An example will hopefully make it clearer:
select json_object('{1, 2, 3, "a", "b", "c"}');
--> {"1" : "2", "3" : "a", "b" : "c"}
--> {"1" : "2", "3" : "a", "b" : "c"}
--> one dimensional even numbered text array
select json_object('{1, a}', '{b, c}');
--> {"1" : "b", "a" : "c"}
--> two dimensional where inner array has two elementsHow to create index in a JSONB column?
We can use GIN indexes to index a JSONB data type, which supports ‘@>’, ‘?’, ‘?&’, ‘?|’ operators using json_ops which is the default method.
create index on schema.tablename using gin(jsonb_column json_ops);
If you find the size of the index a bit large, and in your query, if you mostly use ‘@>’ operator, you can use json_path_ops instead, which only supports the ‘@>’ operator and are substantially smaller.
create index on schema.tablename using gin(jsonb_column json_path_ops);
Be First to Comment