Understanding Operations on Json Data Types With Postgresql

This is a follow-up article to an earlier published one titled “**Understanding and Working with JSON Data Types with PostgreSQL”.**

PostgreSQL provides some operators for working with json and jsonb data. The best way to review these operators is by memorising their use. There aren’t names but symbols so hold on tight, while I write down a couple I think you are more likely to use them right away.

JSON Data Type Operations

A few of the operators apply to both json and json data types. I will place both syntaxes to indicate that an operator applies to both.

jsonb -> integer -> jsonb / json -> integer -> json

Fetch the nth element from a JSON array. Negative nth starts from the end. Remember arrays start from a 0 index.

SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb -> 2
# Output: {"c":"baz"}

SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb -> -1
# Output: {"c":"baz"}

SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb -> -3
# Output: {"a":"foo"}

jsonb -> text → jsonb / json -> text → json

Get the object field with the given key. Trying to access a key that doesn’t exists will return a null.

SELECT '{"person": {"age": 23, "height": 1.80}}'::jsonb -> 'person'
# Output: {"age": 23, "height": 1.80}

jsonb ->> integer → text / json ->> integer → text

Get the nth element of a JSON array as text

SELECT '[1, 2, 3, 4, 5]'::jsonb ->> 2
# Output: 3

jsonb ->> text → text / json ->> text → text

Get the object field with the given key as text.

SELECT '{"age": 23, "height": 1.80}'::jsonb ->> 'age'
# Output: 23

jsonb #> text[] → jsonb / json #> text[] → json

Get the sub-element from the specified path where path element can be field keys or array index.

SELECT '{"person": {"age": 23, "music": {"genre": ["pop", "kpop", "jazz"]}}}'::json #> '{person,music,genre}'
# Output: ["pop", "kpop", "jazz"]

SELECT '{"person": {"age": 23, "music": {"genre": ["pop", "kpop", "jazz"]}}}'::json #> '{person,music,genre, 1}'
# Output: "kpop"

jsonb #>> text[] → text / json #>> text[] → text

Fetch the sub-element as text.

SELECT '{"person": {"age": 23, "music": {"genre": ["pop", "kpop", "jazz"]}}}'::json #>> '{person,music,genre, 1}'
# Output: kpop

jsonb @> jsonb → boolean

Is the object on the left contain the object on the right?

SELECT '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb # Output: true

jsonb <@ jsonb → boolean

The inverse of the above. Does the object on the right contain the object on the left?

SELECT '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb # Output: true

jsonb ? text → boolean

Used to check if a string exists at the top level as key.

SELECT '{"age":17, "height":2.0}'::jsonb ? 'age'
# Output: true

jsonb ?| text[] → boolean

Do any of the strings in the array exists as top level keys?

SELECT '{"age":17, "middle_name": "middleName", "first_name": "Fred"}'::jsonb ?| array['middle_name', 'high_score']
# Output: true

jsonb ?& text[] → boolean

Do all the strings in the array exists as keys?

SELECT '{"first_name": "Alfred", "last_name": "Bruno"}'::jsonb ?& array['first_name', 'last_name']
# Output: true

jsonb - text → jsonb

Delete key from JSON object.

SELECT '{"a": "b", "c": "d"}'::jsonb - 'a'
#Output: {"c": "d"}

jsonb - text[] → jsonb

Delete all keys in the array list.

select '{"a": "b", "c": "d", "e": "f"}'::jsonb - '{a,c}'::text[]
# Output: {"e": "f"}

jsonb @@ jsonpath → boolean

Does the jsonpath return any result?

SELECT '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2'

There are lot of JSON operations and it doesn’t make sense to list all of them as you will barely use them in most of your day to day.

For a complete list of JSON based operations visit the documentation page: JSON Functions and Operators.

How about Indexing?

It only makes sense that we should have a way to index routinely searched keys in a JSON data column. Postgres makes it possible to use GIN indexes to efficiently search for keys and values. Postgres provides two GIN operator classes wit different trade offs.

The default GIN index for jsonb allows querying using existence operators: ?, ?| and ?&, containment operators: @> and jsonpath match operators @? and @@.

Assuming we have a table that stores some API response from a third party data provider that holds records on books, we could create an appropriate index for searching for tags.

{
  "id": 123,
  "title": "Book title",
  "tags": ["Drama", "Fiction", "Love"],
  "Author": "Andrew Parks"
}

A common search would be to look for all books that have a certain tag. The syntax for the GIN index is described below.

CREATE INDEX idxgintags ON books USING GIN ((jdoc -> 'tags'));

This allows us to write an efficient query to easily fetch all books with Fiction tags.

SELECT * FROM books where jdoc->'tags' ? 'Fiction';

The above query will get the database to use the tag index to speed up your search.

To learn more about GIN index for JSON data types including json_path_ops type index, read the docs - Jsonb Indexing.

Conclusion

Understanding and working with JSON data types in PostgreSQL can greatly enhance your ability to store and query complex data structures. PostgreSQL provides a variety of operators and functions for manipulating json and jsonb data, allowing for efficient and flexible data management. Additionally, using GIN indexes can significantly improve the performance of JSON queries, particularly when working with large datasets. By familiarizing yourself with these tools and techniques, you can leverage the full potential of JSON in PostgreSQL for your data-driven applications.